Application.VLookup loop not working.

[bgagan911]

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

 

[AlphaFrog]

You haven't set a value for LastCellInColumn. So your For\Next loop runs from row two to row zero.

Also, it's not clear why you are using VLookup. You are looping through each cell in column C and testng if it equals the value 4, Then you are using VLookup to find the value 4 in column C (which you've already done) and return the value from column G. Why don't you just offset from column C to column G each time the value from column C equals four?

Sub CalculateApril()

    Dim myRange As Range, cell As Range
    Dim Lastrow As Long
    Dim isDecomm As Variant
    
    With ThisWorkbook.Worksheets("MSL")
        Lastrow = .Range("C" & Rows.Count).End(xlUp).Row
        Set myRange = .Range("C2:C" & Lastrow)
    End With
       
    For Each cell In myRange
        If cell.Value = 4 Then
            isDecomm = cell.Offset(0, 4).Value  ' offset to column G
            Debug.Print isDecomm
            'MsgBox isDecomm
        End If
    Next cell
   
End Sub

If you have a long list of values in column C, a faster method would be to filter column C for all the same values (4) using AutoFilter and then do what you need to just the filtered results in column G.

 

[bgagan911]

Thanks for a different perspective to the problem. I'm more of a formula guy than into coding :P So, I had vLookup in my mind to try. I'll work using the offset and will revert in case everything works fine.

Thanks for help

 

[Marcelo Branco]

Hi,

AlphaFrog's solution is better, but for your information if you want to use VLookup i think the proper way is

Application.WorksheetFunction.VLookup(....

not Application.VLookup

M.

 

[bgagan911]

Thanks a tonne! Working perfectly...!!!