excel - VBA counter based on date -

excel - VBA counter based on date -

i have created userform track service requests , having problem lastly part.

there 2 columns in question first "a" service id sec "b" date. want "b" today's date , "a" start @ 1 , count up, when add together new request tomorrow want "a" start @ 1 again.

here code column "a", when new record created "a" goes when date changes not start @ 0. ideas?

'service id 'check row above 'if "date" out set "1" 'if yesterday out set "1" 'if today ouput + 1 if (sheet1.cells(rows.count, "b").end(xlup).value) = "date" serviceorder = "1" elseif (sheet1.cells(rows.count, "b").end(xlup).value) < date serviceorder = "1" elseif (sheet1.cells(rows.count, "b").end(xlup).value) = date serviceorder = (sheet1.cells(rows.count, "a").end(xlup).value) + 1 else end if

thanks in advance.

why not create column a countif formula show number of times date in corresponding column b cell shows in of column b? way when date changes, auto reset 1 , each new time request entered same day, id increment 1. vba formula be:

with sheet1.cells(rows.count, "a").end(xlup).offset(1) .formula = "=countif(b:b,b" & .row & ")" 'uncomment next line if want column contain values instead of formulas 'if uncomment it, create sure have set date in b column entry '.value = .value end

excel vba