In the code below, I look for empty cells in cRange. Each empty cell should be filled with a value based on a VLOOKUP. The value I am looking up in rngLookup is the first five characters of the string immediately to the left of cRange.Cells(x).

The macro is throwing a "Wrong number of arguments or invalid property assignment" error for the line with the VLOOKUP formula. I cannot figure out what is wrong with my syntax. Please help.

Dim rngLookup As Range
Set rngLookup = Sheets("Account Descriptions").Range("A2:B468")

LastRow = Sheets("Summary").Range("B6").End(xlDown).Row
Set cRange = Sheets("Summary").Range("F6:F" & LastRow)
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        If IsEmpty(.Value) Then
            .Formula = Application.vLookup(rngLookup, .Left(ActiveCell.Offset(0, -1), 5), 2, False)
        End If
    End With
Next x

CodePudding user response：

• Your argument order is off: lookup value first, lookup range second.
• .Left is a property of the range; you want the Left function.
• You're writing a value, not a formula, so use .Value.
• Don't use ActiveCell.

.Value = Application.Vlookup(Left(.Offset(,-1).Value, 5), rngLookup, 2, False)