Lookup with Excel VBA

[rollingzep]

I have two sheets on the same WB.
WS, Original contains CUS_ID and MARKET_DES. WS, Test contains few fields like product, and MARKET_DES

I need product from Test to look up the values of CUS_ID of Original and fill the ST field in MARKET_DES with the values of MARKET_DESC from Original.

I am looking to do this using Excel VBA since I have other code for other functionalities.

I have pasted a sample of data.

I downloaded XL2BB but was unable to add it as ADD-IN on my work laptop

 

[Joe4]

I would recommend just using VBA to populate that column with a simple VLOOKUP or INDEX/MATCH formula.

It would look something like this:

Sub MyMacro()

    Dim lr As Long
    
'   Find last row in column A on "Test" sheet with data
    lr = Sheets("Test").Cells(Rows.Count, "A").End(xlUp).Row

'   Populate column B with formula
    Sheets("Test").Range("B2:B" & lr).FormulaR1C1 = "=VLOOKUP(RC[-1],Original!C[-1]:C,2,0)"

End Sub

 

[rollingzep]

I would recommend just using VBA to populate that column with a simple VLOOKUP or INDEX/MATCH formula.

It would look something like this:

Sub MyMacro()

    Dim lr As Long
  
'   Find last row in column A on "Test" sheet with data
    lr = Sheets("Test").Cells(Rows.Count, "A").End(xlUp).Row

'   Populate column B with formula
    Sheets("Test").Range("B2:B" & lr).FormulaR1C1 = "=VLOOKUP(RC[-1],Original!C[-1]:C,2,0)"

End Sub

Hi,

Can you explain what C refers to? VLOOKUP(RC[-1],Original!C[-1]:C,2,0)"

Since I need values from column B in Original to be populated in col B of Test, C[-1] is col B of Original?

So if in Original, the values are in col D, then the formula will be "=VLOOKUP(RC[-1],Original!E[-1]:E,2,0)"?

 

[Joe4]

Can you explain what C refers to? VLOOKUP(RC[-1],Original!C[-1]:C,2,0)"

Since I need values from column B in Original to be populated in col B of Test, C[-1] is col B of Original?

So if in Original, the values are in col D, then the formula will be "=VLOOKUP(RC[-1],Original!E[-1]:E,2,0)"?

No, the "C" has nothing to do with Column letter C.
It is R1C1 reference (where "R" stands for "Row" and "C" stands for "Column").

See here: Excel Formulas - A1 or R1C1 Notation

Note that the good news is you do not even need to know how to write this, you can let Excel do it for !
All you need to do is turn on your Macro Recorder, and record yourself manually entering the formula in the first cell (where you want it to go).
Then, you stop the Macro Recorder and view the code you just recorded - it will give you the formula EXACTLY as you need it (since it is using relative referencing).
So then you just copy/paste that into your VBA code.

By the way, did you try running my code?
If you did, and the structure you posted in your original post is accurate, I think you will find it does exactly what you want.