Change data in column based on vlookup vba between multiple worksheets

Question

I have data in column H in sheet "Example" that I would like to change based on the results of a vlookup to sheet "List".

So the vlookup would be: =VLOOKUP(H23,List!A:C,3,0).

However, I have another macro that I'm calling in this workbook, so I'd like to combine several vba macros into one.

So I'd like the data in column H in sheet "Example" to update based on the results of the vlookup to sheet "List".

Is that possible?

Any help would be much appreciated.

Parfait Parfait · Accepted Answer · 2015-08-09T23:19:51

Simply use the corresponding VLookup() function in VBA:

' ITERATE THROUGH EACH ROW OF COLUMN H
For i = 1 to lastrowinColB
  Sheets("Example").Range("H" & i)= Application.VLookup( _
                                      Sheets("Example").Range("H23"), _
                                      Sheets("List").Range("A1:C500"),3,0)

Next i

Adjust above as needed. I bound the lookup array A1:C500 instead of A:C that may have VBA run to millionth limit row. Also, check reference of lookup value H23 as it may be circular reasoning, considering you intend to update values in column H.

Change data in column based on vlookup vba between multiple worksheets

1 Answers