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.

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

etc.

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 Next End SubPerhaps 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.

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 170pProduct 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

Please click on the blue lineat 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.

Thanks.

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

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History