Vlookup Loop Not Working 1

[Rzrbkpk]

I'm attempting to loop through each cell in a column and complete a vlookup for each value. My syntax doesn't seem to be correct, because I get a 'run time error 1004' on the vlookup line everytime. Any help would be appreciated.

    Sub CalcAging()
    
    Dim aCol As Long
    Dim aCell As Range
    Dim Lastrow As Long
    Dim Result As Variant
    
    Set ws = Sheets("Raw_Data")
    Set ws2 = Sheets("International_Alignment")
    
    ws.Activate
            
    With ws
    
    Lastrow = ActiveSheet.UsedRange.Rows.Count
        
    Set aCell3 = ws.Rows(1).Find("Country")
    aCol3 = aCell3.Column
    
    Columns(aCol3).Offset(0, 1).Select
    Selection.Insert Shift:=xlToLeft, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.NumberFormat = "General"
      
        
        For Each c In Range(Cells(2, aCol3), Cells(Lastrow, aCol3))
            If c.Value = "United States" Then
            c.Offset(0, 1) = "United States"
            Else
            c.Offset(0, 1) = Application.WorksheetFunction.VLookup(ws.Range(Cells(2, aCol3)), ws2.Range("A2:C57"), 3, False)
            End If
        Next
End With

End Sub

 

[SkipVought]

hi,

The main problem is in your lookup VALUE, when should be c.Value.

Also with some additional cleanup...

Sub CalcAging()
    
    Dim aCol As Long
    Dim Lastrow As Long
    
    Set ws2 = Sheets("International_Alignment")
            
    With Sheets("Raw_Data")
    
        Lastrow = .UsedRange.Rows.Count
            
        aCol3 = .Rows(1).Find("Country").Column
        
        With Columns(aCol3).Offset(0, 1)
            .Insert Shift:=xlToLeft, CopyOrigin:=xlFormatFromLeftOrAbove
            .NumberFormat = "General"
        End With
        
        For Each c In Range(.Cells(2, aCol3), .Cells(Lastrow, aCol3))
            If c.Value = "United States" Then
                c.Offset(0, 1) = "United States"
            Else
                c.Offset(0, 1) = Application.WorksheetFunction.VLookup([b]c.Value[/b], ws2.Range("A2:C57"), 3, False)
            End If
        Next
    End With

End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Skip, I'd use this:
With [!].[/!]Columns(aCol3).Offset(0, 1)

 

[Rzrbkpk]

Awesome! It works! Thanks for the fix and the additional cleanup.