Develop formula in excel vba

[vba317]

Is it possible to have a vba function insert a formula into a cell automatically? I have a spreadsheet that has 500+ rows in it. Currently I have a formula that I copy and paste in all 500 cells in the H column that is =(E26-C27)/C27. This formula is currently in rows H2:H502? Any help would be apprecaited.
Tom

 

[SkipVought]

Hi,

Need more information about your sheet and how you use it. There are features in Excel that automatically propogate formulas in a table, when you begin entering data in a new row. Check out Data > List - Create List.

Other than that, you could COPY a cell containing the formula to the new cell or new range of cells.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

If the basic formula in H2 is:

=(E2-C3)/C3

Then

Range("H2:H502").formula = "=(E2-C3)/C3"

will do the job - excel will auto increment the row references for you 'cos it's nice like that!


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

vba,

As a rule, when dividing, test the divisor for ZERO and return an appropriate alternate result.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

My spreadsheet is of the Open,high,low and close of a stock price for the last two years. I have to update the spreadsheet every night, to get the current price action. So when I update the sheet with the prices from that day I have created a macro (With your extensive help) that reformats the spreadsheet with different formulas to see where I stand. The formula I described Subtracts the previous close price - the high of the current day. If it is possible to do this in vba I want to create other columns breaking down the price in different manners. If I can't do it than I will continue using the manual way. BTW thanks for all your help in the past Skip. As I was typing I thought would it be possible to use the range function? Example: Range("H2")=(E26-C27)/C27?

 

[SkipVought]

So when I update the sheet with the prices from that day ...

If you define your table using Data > List > Create List then you do NOT need VBA code to do your daily update!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

How does that formula propogate down?

What would be the formula for H3?

If it is the same formula then you can do it in 1 line of code as I have indicated above

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

ANY formula in the table defined in Data > List > Create List is propogated when you TAB to enter data in the next row. Really neat feature I recently discovered.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Skip,
I couldn't get your solution to work. But xlbo's solution did work so I'm sticking with it. Again thanks for your time.


Tom