How do I set up formula in cells via VBA macros

March 26, 2013 at 12:52:09
Specs: Windows 7
I have a number of occasions when I want to set up formula in cells when the change in reference cell is by row but the formula changes by column.
e.g. B100 is +A1; B101 is +B1
As I cannot copy them down I have to enter each one manually, with the posible errors I would like to write a macro which allows me to set these up. Effectively setting up the formula by a macro to avoid errors.

See More: How do I set up formula in cells via VBA macros

Report •

March 26, 2013 at 18:31:44
When you use +A1 and +B1, I assume you mean =A1 and =B1, correct?

The plus sign is a very old school way of writing formulas and isn't seen very much any more, although it still works.

Actually, with the example you show above, you can use other means to "copy them down". This formula, placed in B100, will give you the equivalent of =A1, =B1, =C1 as you drag it down:

=OFFSET($A$1,0, ROW()-100)

Since the ROW() function simply returns the number of the Row it resides in, the above formula is equivalent to this, as you drag it down:

=OFFSET($A$1,0,0) --> =A1
=OFFSET($A$1,0,1) --> =B1
=OFFSET($A$1,0,2) --> =C1
=OFFSET($A$1,0,3) --> =D1

To set up different formulas with a macro - meaning different formulas each time - you would need to know how to write VBA code, because you have to build the formula within VBA and then place it in the cells, incrementing the row and/or column for each cell. There's no "generic" formula writing code.

For example, the code for your example above would be as follows, placing =$A$1,$B$1,$C$1, etc. in B100:B109.

Sub A1B1_Formula()
  For rw = 100 To 109
    Cells(rw, 2).FormulaR1C1 = "=R1C" & rw - 99
End Sub

Perhaps if you gave us some more examples of what you are trying to do, we could be of more help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 27, 2013 at 13:01:30
How do I change row the data comes from. I am trying to change the data from column to row, so I can use it in sum products formula?

Cost of Sales Stock Cost1 Cost2 Cost3 Cost4
Product 1 10p 20p 30p 40p
Product 2 1p 2p 3p 4p
Product 3 120p 130p 140p 150p
Product 4 130p 140p 150p 160p
Product 5 140p 150p 160p 170p

Product 1 Product 2 Product 3
Cost1 10.00 1.00 120.00
Cost2 20.00 2.00 130.00
Cost3 30.00 3.00 140.00
Cost4 40.00 4.00 150.00
Cost5 50.00 5.00 160.00
Cost6 60.00 6.00 170.00

Report •

March 27, 2013 at 13:25:57
Please click on the blue line at the bottom of this post and read the instructions on how to post example data in this forum.

Then please repost your data, including Column Letters and Row Numbers. A sample formula to show what you are trying to acheive couldn't hurt.

Keep in mind that you know exactly what you are trying to do and we're trying to figure it out based solely on what you post. The more details you can provide the better.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question