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