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>

 

[rollingzep]

Or is there an alternative way?

 

[mikerickson]

...will that work?

What happened when you tried it?

 

[rollingzep]

What happened when you tried it?

It did not fill the rows with values in Col, BE.

I even tried
Sheets("TSS Trans").Range("BE2:BE" & arr).FormulaR1C1 = "=VLOOKUP(RC[-1],HQLA!C[-26]:C,29,0)"

It still did not work.

 

[rollingzep]

I am not getting any code error when i compile. but the code is not working.
I use breakpoint but though the value of i=2 (second row), it is blank

 

[Fluff]

Shouldn't you be using

Set rng = ws.Range("AG2:AG" & Cells(Rows.Count, 1).End(xlUp).Row)

although you said you wanted to fill col BE

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help with Lookup
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[rollingzep]

It did not fill the rows with values in Col, BE.

I even tried
Sheets("TSS Trans").Range("BE2:BE" & arr).FormulaR1C1 = "=VLOOKUP(RC[-1],HQLA!C[-26]:C,29,0)"

It still did not work.

Shouldn't you be using

Set rng = ws.Range("AG2:AG" & Cells(Rows.Count, 1).End(xlUp).Row)

although you said you wanted to fill col BE

That was a typo. I did use AG2:AG, but that did not work.

The values of AG2:AG from TSS_Trans should look up to col C2:C in HQLA and fill values of AC in HQLA to BE2:BE in TSS_trans.

Though I downloaded the XL2BB Add-In, the install did not work on my machine and I am unable to post sample data

 

[Fluff]

In that case your code makes no real sense as you are trying to put the formula in col AG & using AF to lookup the values in col G. Also that formula will never work as you are trying to get the value from the 29th col, but there are not 29 columns in the formula.

I will have another look at it later if I get time, otherwise it will be tomorrow.

 

[Fluff]

Maybe replace the code you posted with

Set Ws = wbexcel.Sheets("TSS Trans")
Ws.Range("BE2:BE" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-25],HQLA!C3:C29,29,0)"