Lookup Using RC

[rollingzep]

Hi,

<p>
I have two sheets on the same WB.

One is TSS and other is HQLA

I need values from AG in TSS_Trans to look up to values in Col C in HQLA and fill up Col BE in TSS.

Few of the values in Col C in HQLA have extra characters for which I use the Len and Mid functions

For example, some values are CA44054J2002 while the actual value is 44054J200.

This is my code. I am not sure if I can use the VLOOKUP with FormulaR1C1 as below. will that work?

<code>

Set ws = wbexcel.Sheets("TSS Trans")
Set wsA = wbexcel.Sheets("HQLA")

arr = wsA.Range("C2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
Set rng = ws.Range("AG2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
For i = 1 To UBound(arr)
For c = 1 To rng.Rows.Count
If Len(arr(i, 1)) > 9 Then arr(i, 1) = Mid(arr(i, 1), 3, 9)
If arr(i, 1) = rng.Cells(c) Then
rng.Cells(c).FormulaR1C1 = "=VLOOKUP(RC[-1],HQLA!C[-26]:C,29,0)"
End If
Next c
Next i

</code>

</p>

 

[Fluff]

Maybe

Ws.Range("BE2:BE" & Ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-24]&""*"",HQLA!C3:C29,27,0)"

 

[rollingzep]

Maybe

Ws.Range("BE2:BE" & Ws.Cells(Rows.Count, "AG").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-24]&""*"",HQLA!C3:C29,27,0)"

Did not help

 

[Fluff]

In that case can you post some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[rollingzep]

In that case can you post some sample data from both sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Test_0816.xlsx
	F
27	50010
TSS Trans

Test_0816.xlsx
	H
24	529900K9B0N5BT694847
HQLA

 

[rollingzep]

Test_0816.xlsx
	B
5	8/12/2022 0:00
TSS Trans

Test_0816.xlsx
	H
24	529900K9B0N5BT694847
HQLA

 

[Fluff]

I need the values from col C of HQLA sheet & from col AG on the other sheet, preferably about 20 samples of each including some that match.

 

[rollingzep]

I need the values from col C of HQLA sheet & from col AG on the other sheet, preferably about 20 samples of each including some that match.

Your code worked correctly after I made a slight change at my end.

Thanks a lot for your time and help!!!

 

[Fluff]

Glad it's sorted & thanks for the feedback.