VBA If statement applying Vlookup to whole range

Question

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
Next

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

Yes, you need offset, but also the first argument in VLOOKUP can't be a range of cells. — SJR
You're also inconsistent in your sheet referencing - need to tidy that up too. — SJR
My suggestion in all uses of VLookUp is to use Index/Match. Rather than finding an overall range and trying to identify columns from your start of said range, you just put in the specific data ranges you want, e.g., Application.Index(OutputRange, Application.Match(SearchByTerm, SearchRange, Arg3)), noting that Arg3 relates to match Term in Range exactly or not. An example of this being filled in is Application.Index(Columns("P"), Application.Match(Cells(1,1).Value, Columns("A"),0)) — Cyril

CLR CLR · Accepted Answer · 2019-05-09T15:27:19

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

Try:

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)

VBA If statement applying Vlookup to whole range

1 Answers