VBA Formula issue

[EB08]

In short, I need to enter a formula into a variable cell via VBA. I have done similar things to this but for some reason, can't get this one to work. one of the cells in the formula itself also come from a variable. Any help is appreciated!

My most recent attempt is:

Cells(52, startcolumn).Formula = "=(" & Cells(50, startcolumn) & "/e50)-1"

startcolumn already has the value of 11 in this example.

 

[Norie]

What value is actually in Cells(50, startcolumn)?

If it's empty the formula will end up as =(/e50)-1, which isn't a valid formula.

 

[EB08]

Completely dropped the ball on that one! I had an empty cell currently in place. On another note, I havent decided which way would be best yet but would i be able to use the cell reference in the formula so it wouldn't hard code the value (referring to cells(50,startcolumn))? so in this case, i would like the formula to possibly be entered as =(k50/e50)-1 instead of (value of k50 hardcoded/e50)-1. Thanks again.

 

[Norie]

Just add address.

Cells(52, startcolumn).Formula = "=(" & Cells(50, startcolumn).Address & "/e50)-1"

Note the default return for Address is absolute. eg $K$50.

There are however arguments you can use to change it to use relative referencing.

 

[EB08]

Great...Thank you, this was a lagging piece of something I was building, now it's fixed!