Consulting

Results 1 to 2 of 2

Thread: incrementing a set string by1

  1. #1
    VBAX Newbie
    Joined
    Jan 2022
    Posts
    1
    Location

    incrementing a set string by1

    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

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,644
    Sub M_snb()
      For j = Date To Date + 10
        c00 = c00 & "_" & Sheet1.Cells.Find(j, , , 1).Address
      Next
    
      msgbox c00
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •