Create formula below specific text

[Panoos64]

Hi all, i would like to create a VBA code so that to run through row "1" and where find the text "Bank list' should create the below formula:

"=VLOOKUP(RC[-10],'[PAYMENT LIST.xlsx]Sheet1'!R4C1:R5000C32,25,0)"

Kindly note that the formula should begin from row "2" till last fulfill row based on col. "K".
Below is an extract of my spreadsheet in which i want to add the above formula below Bank list column.

Thanking you in advance

<colgroup><col style="mso-width-source:userset;mso-width-alt:2389;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:2844;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:4380; width:92pt" width="123" span="2"> </colgroup><tbody>
[TD="width: 67, align: center"][/TD]
[TD="width: 80, align: center"]K[/TD]
[TD="width: 123, align: center"]L[/TD]
[TD="width: 123, align: center"]M[/TD]

[TD="class: xl67, width: 67, align: center"]1[/TD]
[TD="class: xl68, width: 80, align: center"]CODE[/TD]
[TD="class: xl68, width: 123, align: center"]Bank list
[/TD]
[TD="class: xl68, width: 123, align: center"]Variance[/TD]

[TD="class: xl80, width: 67, align: center"]2[/TD]
[TD="class: xl72, align: center"]2002
[/TD]
[TD="class: xl77, width: 123, align: center"][/TD]
[TD="class: xl77, width: 123, align: center"] [/TD]

[TD="class: xl80, width: 67, align: center"]3[/TD]
[TD="class: xl72, align: center"]2008
[/TD]
[TD="class: xl71, width: 123, align: center"]
[/TD]
[TD="class: xl71, width: 123, align: center"] [/TD]

[TD="class: xl80, width: 67, align: center"]4[/TD]
[TD="class: xl72, align: center"]2010[/TD]
[TD="class: xl71, width: 123, align: center"] [/TD]
[TD="class: xl71, width: 123, align: center"]
[/TD]

[TD="class: xl80, width: 67, align: center"]5[/TD]
[TD="class: xl72, align: center"]2011[/TD]
[TD="class: xl71, width: 123, align: center"] [/TD]
[TD="class: xl71, width: 123, align: center"] [/TD]

</tbody>

 

[Tim_Excel_]

Dim colNum As Integer

With Workbooks(NAME).Worksheets(NAME) 'edit names
LRow = .Cells(.Rows.Count, "K").End(xlUp).Row

colNum = WorksheetFunction.Match("Bank list", ActiveWorkbook.Sheets(sheetname).Range("1:1"), 0) 'edit the sheet name
.Range(Cells(2,colNum), Cells(LRow,colNum).FormulaR1C1 = "=VLOOKUP(RC[-10],'[PAYMENT LIST.xlsx]Sheet1'!R4C1:R5000C32,25,0)"

end with

Let me know if this helps you

 

[Panoos64]

Hi Tim, It doesn't work. It appears a message which write the belows:

Compile error
Syntax error
Also the below command appears as red.

.Range(Cells(2,colNum), Cells(LRow,colNum).FormulaR1C1 = "=VLOOKUP(RC[-10],'[PAYMENT LIST.xlsx]Sheet1'!R4C1:R5000C32,25,0)"

Many thanks for your support

 

[Tim_Excel_]

Dim colNum As Integer


With ThisWorkbook.Worksheets(1) 'edit references to your preferences
LRow = .Cells(.Rows.Count, "K").End(xlUp).Row


colNum = WorksheetFunction.Match("Bank list", .Range("1:1"), 0) 
.Range(.Cells(2, colNum), .Cells(LRow, colNum)).FormulaR1C1 = "=VLOOKUP(RC[-10],'[PAYMENT LIST.xlsx]Sheet1'!R4C1:R5000C32,25,0)"




End With

Use this line instead, tired Tim wrote the first code which has been improved by lesser tired Tim. Tested and works.

 

[Panoos64]

Hi Tim, it blocks on the below point.

colNum = WorksheetFunction.Match("Bank list", .Range("1:1"), 0)

Please note that i prefer it to run on "ActiveWorkbook" and "ActiveSheet" not using workbook name or sheets names.

Thanks once again
Hv a nice day

 

[Tim_Excel_]

What exactly is the error it is giving you? If it is a Subscrip Out of Range error, then you may check if the value you are looking for is actually "Bank list". This error would mean Excel is not finding that value in the top row.

Furthermore, I would not recommend using Activeworkbook, as it is asking for errors. I would endorse the use of variables or the actual name if referring to anything.

 

[Panoos64]

Apologies Tim, i had a mistake to description Bank List. i had "List" with capital the first letter. it works perfect now and thank you so much for your time spent for my project. Hv a great day!

 

[Tim_Excel_]

No problem, good luck with your project