Find specific text "Occupancy" and prepare formula in adjacent cell

[Panoos64]

Hi all, i would like to write a vba so that to run through col. "B" and where find the text "Occupancy" should write down a formula in col. "C" as my below extract. Note that the row of text "Occupancy" change each time when i download the data through accounting system.

Thanking you in advance

<tbody>
[TD="class: xl63"]

<tbody>
[TD="class: xl65, align: center"][/TD]
[TD="class: xl65, width: 213, align: center"]B[/TD]
[TD="class: xl67, width: 592, align: center"]C[/TD]

[TD="class: xl65, align: center"]135[/TD]
[TD="class: xl65, align: center"]Occupancy[/TD]
[TD="class: xl67, align: center"]=VLOOKUP($D$1;[TABLE.xlsx]Sheet1!A3:M7;MATCH($D$6;[TABLE.xlsx]Sheet1!A1:M1;0);0)
[/TD]

[TD="class: xl65, align: center"]136[/TD]
[TD="class: xl65, align: center"][/TD]
[TD="class: xl67, align: center"][/TD]

</tbody>

[/TD]
[TD="class: xl65, width: 85"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl65, align: right"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl65"][/TD]

</tbody>

 

[Vbagreenhorn1]

something like this:

Range("B1:B1000").Find("Occupancy").Offset(, 1).FormulaLocal = "=VLOOKUP($D$1;[TABLE.xlsx]Sheet1!A3:M7;MATCH($D$6;[TABLE.xlsx]Sheet1!A1:M1;0);0)"

 

[footoo]

Sub ft()
[B:B].Find("Occupancy")(1, 2).Formula = _
    "=VLOOKUP($D$1;[TABLE.xlsx]Sheet1!A3:M7;MATCH($D$6;[TABLE.xlsx]Sheet1!A1:M1;0);0)"
End Sub

 

[Panoos64]

Hi foot, It works perfect! Thank you so much for your support and your time. In addition i express my best wishes for the new year!

 

[Panoos64]

Hi Vbagreen, The code block and it appears the following message:
"Run-time error 1004"
"Application-defined or object-defined error"

However i would like to thank you for your support and my best wishes for the new year