rollingzep
Board Regular
- Joined
- Nov 18, 2013
- Messages
- 214
- Office Version
- 365
- Platform
- Windows
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>
<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>