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
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