Application.VLookup loop not working.

**bgagan911** · 03-11-2011, 09:56 PM

I've to iterate through each cell in "Column C" in downward direction.

I've to fetch values from the corresponding cells for which I'm using vLookup. I need to fetch multiple values so I would be using more vLookups in the loop after I get this working.

All these fetched values would be clubbed and based on conditions counters ,like one of them being "A" would be updated and summed at last to find the sum of each counter variables.

Here I'm trying to do is use loop for application "VLookup", but for some reasons it is not working.

I've tried using
..VLookup( Range("C" & i), ...
..VLookup("C" & i, ...
..VLookup(Cells(i, "C"), ...

i is the counter and C is the column. Could you please help me with it? I've been searching for help from past 8 hours. Finally I had to paste. I'll attach the file too.

Sub CalculateApril()
    
Dim i As Integer, A
A = 0
Dim isDecomm
Dim myRange As Variant, mySelectedArea

mySelectedArea = ThisWorkbook.Worksheets("MSL").Range("C2:G22")
   
    For i = 2 To LastCellInColumn - 1
    With ActiveSheet
    ' Define Range
    myRange = "C" & i
        '   Issued in April. Checks for the Month
        If Range(myRange).Value = 4 Then
            A = A + 1
            '   Are they Decommissioned ? Yes = Ignore, No - Count
            isDecomm = Application.VLookup(Cells(i, "C").Value, Range("C2:G22"), 5, False)
            Debug.Print Cells(i, "C").Value
            'MsgBox isDecomm
        End If
    End With
    '   Increment i/Row
    Next i
   
End Sub

**Leith Ross** · 03-11-2011, 10:43 PM

Hello bgagan911,

Welcome to the Forum!

The syntax for VLookup in VBA is different than that for the worksheet formula. For example, your line of code below:

  What you have...
    isDecomm = Application.VLookup(Cells(i, "C").Value, Range("C2:G22"), 5, False)


  What you should have...
    isDecomm = Application.VLookup(Cells(i, "C").Value, Range("C2:G22").Columns(5), 1, False)

Additionally, you will need to check if the returned value was an error. Declare the variable that will hold the VLookup result as a Variant. Using the VBA VarType function, check if the result is equal to the constant vbError.

Here is the macro with the corrections added.

Sub CalculateApril()
    
  Dim i As Integer, A
  A = 0
  Dim isDecomm
  Dim myRange As Variant, mySelectedArea

  mySelectedArea = ThisWorkbook.Worksheets("MSL").Range("C2:G22")
   
    For i = 2 To LastCellInColumn - 1
    With ActiveSheet
    ' Define Range
    myRange = "C" & i
        '   Issued in April. Checks for the Month
        If Range(myRange).Value = 4 Then
            A = A + 1
            '   Are they Decommissioned ? Yes = Ignore, No - Count
            isDecomm = Application.VLookup(Cells(i, "C").Value, Range("C2:G22").Columns(5), 1, False)
            Debug.Print Cells(i, "C").Value
            If VarType(isDecomm) = vbError Then
               MsgBox "Not found."
            Else
               MsgBox isDecomm
            End If
        End If
    End With
    '   Increment i/Row
    Next i
   
End Sub