VLookup in VBA for loop failing after first pass

Question

I'm trying to use a FOR loop in VBA to loop through a sheet and use VLookup to determine values based on each range. Basically the sheet is set up where I have 14 ranges, and each range is 2 columns (date and value) set up like this: Sample Data

This code loops through each range and performs a VLookup to return the value, or returns -1 if there's no value.

The problem I'm having is this works with the first row of data, but all of the rows thereafter are returning -1.

Sub Format(inSheet As String, outSheet As String, lastAvail as Date, maxRows as Long)
Do While curDate <= lastAvail
    For x = 2 To (maxRows - 1) * 2 Step 2
        ' Get value of current data series
        Sheets(inSheet).Activate
        Range(Cells(8, x - 1), Cells(8, x)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Set lookupRange = Selection
        val = Application.VLookup(curDate, Worksheets(inSheet).Range(lookupRange.Address), 2, False)
            Sheets(outSheet).Activate
            If IsError(val) Then
                Cells(curRow, x / 2 + 1).Value = -1
            ElseIf IsNumeric(val) Then
                Cells(curRow, x / 2 + 1).Value = val
            Else
                Cells(curRow, x / 2 + 1).Value = Null
            End If
    Next

    curDate = DateAdd("m", 1, curDate)
    Cells(curRow, maxRows + 1).Value = curDate - 1
    curRow = curRow + 1
    val = ""
Loop

Have you tried stepping through it to see what is happening in the loops? — PartyHatPanda
I'm not a 100% sure, but aren't you adding a month on curDate = DateAdd("m", 1, curDate) while your data sample is only a set of days in the same month (January)? — Tom K.
Also, it's best to avoid .Select, which may help generally. — BruceWayne
Apart form anything else, you are changing the lookup range every iteration of the loop: you really only want to change the lookup value (what you are searching for in the table). — matt_black
@BruceWayne just out of curiosity, what would you suggest there? I couldn't figure out a more elegant way to capture the range — Jim S

Jim S Jim S · Accepted Answer · 2016-08-12T15:44:24

Thanks for your help guys, It turns out that when I increment curDate with DateAdd , it changes the format so VLookup doesn't recognize that it's matching the actual data. I converted curDate to a Long right before the VLookup and everything worked itself out.

VLookup in VBA for loop failing after first pass

1 Answers