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

B C 135 Occupancy =VLOOKUP($D$1;[TABLE.xlsx]Sheet1!A3:M7;MATCH($D$6;[TABLE.xlsx]Sheet1!A1:M1;0);0) 136 <tbody> </tbody>

<tbody>
</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