PDA

View Full Version : incrementing a set string by1



gordsky
01-05-2022, 12:53 PM
Hi
I have a macro which searches a wb based on a date and returns the value from column b and the row the date is located in.

I need to run this several times incrementing the day added each time and at present am doing it by writing the following for each occasion and renaming "Ans" each time. The DteRng remains constant

DteLookup = Format(DateAdd("d", 1, Now()), "dd/mm/yyyy")
set DteRng = ws.Range("a5:a" & lrow)

Set Ans= DteRng.Find(What:=DteLookup, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Would there be a way to put this in a loop and store each "Ans" so that it incremented by 1 eg on the first run it would be Ans1 on the 2nd Ans2 and so on storing them until i need to reference them later in the macro (without storing them temporarily in the sheet)
so where i = 1 Ans is actually Ans1 and for i =2 Ans is Ans2
Something like

for i = 1 to 10
DteLookup = Format(DateAdd("d", (i), Now()), "dd/mm/yyyy")
set DteRng = ws.Range("a5:a" & lrow)

Set Ans(i)= DteRng.Find(What:=DteLookup, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
next i

snb
01-06-2022, 02:45 AM
Sub M_snb()
For j = Date To Date + 10
c00 = c00 & "_" & Sheet1.Cells.Find(j, , , 1).Address
Next

msgbox c00
End Sub