VLookup

[rollingzep]

Hi,

<p>
I have a vlookup between two worksheets.

It is as follows

ws.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-22]&""*"",HQLA!C1:C35,35,0)"

How do I modify the above, so that if the length of col, Y is 9, then it will be the above formula or
if the length is 12, then it should be

ws.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).FormulaR1C1 = "=IFERROR(VLOOKUP(""*""&RC[-22]&""*"",HQLA!C3:C33,33,0)

</p>

 

[rollingzep]

Hi,

<p>
I have a vlookup between two worksheets.

It is as follows

ws.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-22]&""*"",HQLA!C1:C35,35,0)"

How do I modify the above, so that if the length of col, Y is 9, then it will be the above formula or
if the length is 12, then it should be

ws.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).FormulaR1C1 = "=IFERROR(VLOOKUP(""*""&RC[-22]&""*"",HQLA!C3:C33,33,0)

</p>

Actually the second formula should be

ws.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-22]&""*"",HQLA!C3:C33,33,0)

 

[rollingzep]

Can I do this way? I am not sure how to construct this code and I need help

<code>
arr = ws.Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
Set rng = wsA.Range("AU2:AU" & 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
wsA.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-22]&""*"",HQLA!C1:C35,35,0)"
Else
wsA.Range("AU2:AU" & ws.Cells(Rows.Count, "Y").End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(""*""&RC[-22]&""*"",HQLA!C3:C33,33,0)"
..................................
......................................................
</code>
Thanks,