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]

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)"

Thanks. Actually the -25 is AF. My data is in AG
I modified it to RC[-24] but it looks at row 1 which is the header rather than row 2

 

[rollingzep]

Thanks. Actually the -25 is AF. My data is in AG
I modified it to RC[-24] but it looks at row 1 which is the header rather than row 2

The RICI formula reads from row 1 which is the header on both sheets rather than row 2, where the data starts. can it be modified?

 

[Fluff]

The formula will look at it's own row, not row 1.

 

[rollingzep]

The formula will look at it's own row, not row 1.

When I use this code, it reads two rows and stops

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

Also, how to use the Mid(arr(i, 1), 3, 9) for the Col C in HQLA?

I apologize if I am bit clueless. But I use the array to read the values from Col C and get the 9 characters to match AG values in TSS Trans.

This is how the TSS Trans and HQLA are

ans

TSS Trans



HQLA



HQLA



Can I use the code as below?

<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("A2: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
ws.Range("BE2:BE" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-24],HQLA!C3:C29,29,0)"
End If
Next c
Next i

</code>

 

[Fluff]

If the formula is going into two cells, that would suggest that column A is totally empty.
Try it like

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

 

[rollingzep]

If the formula is going into two cells, that would suggest that column A is totally empty.
Try it like

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

I commented all code except

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

but it is still only reading two rows. and also BE starts at row 1, the header row, which is incorrect.

Not sure why?

 

[Fluff]

That is not the code I suggested.

 

[rollingzep]

That is not the code I suggested.

I tried this as well with similar results.

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

 

[Fluff]

Oops, it should be

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

 

[rollingzep]

Oops, it should be

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

That worked! Thank you.

But it returns #N/A on all rows, because I suspect it is looking for a match (Col C in HQLA to Col AG in TSS Trans). That is why I was using the mid function to read Col C2:C on HQLA

How do i use the Mid(arr(i, 1), 3, 9) in the VLookup