Dynamic cell refferemce in a VBA formula

[Sorinsun]

Hi,

At the top I enter the value of a cell B1: "Product List".
Then I am populating the spreadsheet with a number of rows that is always variable.
Then at the bottom I need to enter a formula that needs to output the following result: "End of Product List" where "Product List" is the dynamic reference to B1.
If the user then erases any number of rows in the spreadsheet, the bottom still reads the same.
If the user changes the value of B1 then I need the bottom to be able to update the change so a dynamic link to the cell B1 is necessary.

then of course, after I figure this out, I need to populate adjacent the bottom cell in a similar way with a dynamic sum.

Any help ? I tried Formula, FormulaR1C1 but all seem to be stuck on hard coded references E.g.
Range("B40").FormulaR1C1 = "=""End of "" & R[-39]C"
In simple words, how could I replace the [-39] with a variable I set programmatically

Thanks !

[Norie]

Why not hardcode B1?

Please Login or Register  to view this content.

[Sorinsun]

Thanks Norie ! You made my day !
Here is how I solved it based on your suggestion

MyRange = "B1"
Range("B40").Formula = "=""End of "" & " & MyRange


I can control programmatically the locations of the cell B1 and B40. I tried to work with the double quotation marks but for some reason it did not work for me until you showed me how ... lol !