incrementing and storing a result without using cell

[gordsky]

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

Ive tried the above without sucess.

Thanks in advance for any advice / pointers you can give

 

[tonyyy]

You could store your returns in an array; for example, by declaring ans as an array with 10 rows...

Dim ans(10) As Variant

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

For i = 1 To 10
    Debug.Print ans(i)
Next i

Cheers,

Tony

 

[gordsky]

Thanks Tonyyy exactly what I needed. I was just missing the numbers in the Dim.