I'm trying to update the values within a range based on an adjacent column's values, with the updated values pulled from another worksheet with a VLookUp. I'm running into an issue where the VLookUp is being applied to the whole range whether or not the adjacent column displays the correct value.

I've tried using an offset instead of a range and various tweaks to the statement structure but I just get different errors EG: the data updates but only displays the first value of the VLookUP data for all matching criteria.

Dim S1 As Worksheet
    Set S1 = Worksheets("Sheet1")
Dim rcga As Worksheet
    Set rcga = Worksheets("RCGA")
TABLEROW = Range("A" & Rows.Count).End(xlUp).Row
Dim REFRNG As Range
Dim CHNGRNG As Range
Set REFRNG = Range("L2:L" & TABLEROW)
Set CHNGRNG = Range("J2:J" & TABLEROW)
For Each cell In REFRNG
    If cell.Value = "Trust for RCGA" Then 'currently applies vlookup to whole J range
        CHNGRNG = Application.WorksheetFunction.VLookup(S1.Range("A2:A" & TABLEROW), rcga.Range("A2:P" & TABLEROW), 16, 0)
    ElseIf cell.Value = "0" Then
    End If

I would like to have any values in column J where column L displays "Trust for RCGA" to update to new calculated values on the RCGA worksheet with all values where column L is "0" to remain unchanged

1 Answers


You're inserting the entire column A into the first argument of VLOOKUP.


CHNGRNG = Application.WorksheetFunction.VLookup(REFRNG.Offset(0, -11).Value, rcga.Range("A2:P" & TABLEROW), 16, 0)

Where the cell you want to 'look-up' is 11 cells to the left of Each cell being examined.

Also, as @SJR points out, you should qualify your ranges with the sheet that you intend Excel to use:

TABLEROW = (s1 or rcga).Range("A" & (s1 or rcga).Rows.Count).End(xlUp).Row
Set REFRNG = (s1 or rcga).Range("L2:L" & TABLEROW)
Set CHNGRNG = (s1 or rcga).Range("J2:J" & TABLEROW)