0

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
Community
  • 1
  • 1
Jim S
  • 1
  • 2
  • 1
    Have you tried stepping through it to see what is happening in the loops? – PartyHatPanda Aug 12 '16 at 14:06
  • 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. Aug 12 '16 at 14:10
  • Also, it's [best to avoid `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), which may help generally. – BruceWayne Aug 12 '16 at 14:15
  • 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 Aug 12 '16 at 14:54
  • @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 Aug 12 '16 at 15:45
  • select is a bad habit, not elegant at all.Set lookupRange = Range( "xxx") directly –  Aug 12 '16 at 16:09

1 Answers1

-1

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.

Jim S
  • 1
  • 2