Vlookup Formula in Excel VBA

Question

I have an Excel sheet with locked cells. Some cells are unlocked to input values. There are many values to input therefore I'm trying to write a Macro that will: select the unlocked cells in the Active Sheet and fill the selected cells with a Vlookup formula that looks up the input values based on the first column and relevant rows in the same sheet from a table on a separate sheet. I have tried the below:

Sub SelectUnlockedCells()
'Update 20130830
Dim WorkRng As Range
Dim OutRng As Range
Dim Rng As Range
On Error Resume Next
Set WorkRng = Application.ActiveSheet.UsedRange
Application.ScreenUpdating = False
For Each Rng In WorkRng
    If Rng.Locked = False Then
        If OutRng.Count = 0 Then
            Set OutRng = Rng
        Else
            Set OutRng = Union(OutRng, Rng)
        End If
    End If
Next
If OutRng.Count > 0 Then OutRng = Application.WorksheetFunction.VLookup("A" & ActiveRow.Value, Worksheets(2).Columns("A:C").Select, Worksheets(2).Columns(3).Select, False)
Application.ScreenUpdating = True
End Sub

I know my problem occurs in the last 4 lines in the Vlookup Worksheet Function, because if I say:

If OutRng.Count > 0 Then OutRng = 1 + 1

The unlocked input cells are imputed correctly as 2. Therefore I suspect my Vlookup object selection is not correct.

Any help would be great, thanks!

Kyle Walden Kyle Walden · Accepted Answer · 2020-08-17T06:48:57

Simple solution I found was as I suspected - object referencing inside the vlookup was wrong. Also, the Application Worksheet Function was redundant in this case. In the third last line solution was:

If OutRng.Count > 0 Then OutRng = "=VLOOKUP(A11,'Sheet1'!Table1,3,FALSE)"

Vlookup Formula in Excel VBA

2 Answers