VBA Formula issue

EB08

Active Member
Joined
Jun 9, 2008
Messages
343
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 9, 2008
Messages
343
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Just add address.
Code:
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

Active Member
Joined
Jun 9, 2008
Messages
343
Great...Thank you, this was a lagging piece of something I was building, now it's fixed!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top