Please help - expected record status "missing"
 

I posted two previous queries which to date have gone unanswered. So I will
attempt to ask this question in a different way.

I have a list of accounts with multiple records. I expect a certain status
(i.e "curr") at the last record for each account. If the expected last
record is not there (i.e. status is not "curr") then I would like the
encountered last record to reflect a status of "off". All my coding attempts
have failed so far. I really don't know how to code this and need some help
please.

Please help - expected record status "missing"
 

assume account numbers are in column A and date is in column B. Status goes
in column C

Headers in row 1
Data starts in row 2

Assume the data is sorted by account number and date

Sub MarkAccounts()
Dim rng as Range, cell as Range
Dim dt as Date
' expected date
dt = Date-2
set rng = Range(cells(2,1),Cells(2,1).End(xldown))
for each cell in rng
if cell.Value < cell.offset(1,0) then
if abs(dt-cell.offset(0,1)) = 1 then
cell.offset(0,2).Value = "off"
else
cell.offset(0,2).Value = "curr"
end if
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"Hilton" wrote in message
...
I posted two previous queries which to date have gone unanswered. So I
will
attempt to ask this question in a different way.

I have a list of accounts with multiple records. I expect a certain status
(i.e "curr") at the last record for each account. If the expected last
record is not there (i.e. status is not "curr") then I would like the
encountered last record to reflect a status of "off". All my coding
attempts
have failed so far. I really don't know how to code this and need some
help
please.