Adding formula to multiple cells

[kshora]

Hi

I have a large work sheet with existing data. How can I add a formula to all the data (cells with data) at one time. Entering the formula one cell at a time is not possible e.g there are 10 cells with data and I want to add a same formula say multiply all 10 cells by $A$100, so that every time I change A100 cell value, all the corresponding cell value changes

Help is highly appreciated

Thanks and regards

Shora

 

[SteveO59L]

Add it to the first cell, then use copy & paste ?

 

[Richard Schollar]

Hi Shora

You can probably manage it with a Find/Replace:

1.Select your cells
2. Press Ctrl+H
3. Type = into the Find What box
4. Type =$A$100* in the Replace With box
5. Hit Replace All

Hope this helps!

Richard

 

[kshora]

Hi

But I already have data in these cells, copy and paste will not workk as the data is different in each cell.

Find/Replace does not seem to work as there is no "=" in the cells to replace.

Say I have 28.8 in one cell, 35.2 in another cell, 40.12 in another and so on and I want to multiply them by $A$100 and want to add *$A$100 to all the cells, how do I do that in all the cells at one time

Thanks and regards

Shora

 

[Richard Schollar]

Shora

There's no easy way if you just have values - but you can type say 100 into another cell, copy the cell, select your range and go Edit>PasteSpecial and click on the multiply box and then click OK.

Richard

 

[DominicB]

Goo afternoon kshora

Press Alt + F11 and go to Insert > Module, and copy this code into the window that appears :

Sub test()
For Each UsrCell In Selection
UsrCell.Formula = "=" & UsrCell.Value & "*A100"
Next UsrCell
End Sub

Click the red X in the corner to close. Select all the cells you want to apply the formula to and go to Tools > Macro > Macros and select test from the box and click on Run. You can now delete the macro if you wish.

HTH

DominicB

 

[kshora]

Richard
Thanks for the reply, I had a feeling that this won't be easy, the problem is that the value to multiply also changes and I have to paste special every time, I have around 2500 cells to work with, please let me know if you come up with a better solution.

Thanks and Regards

Shora

 

[Richard Schollar]

There's no native way within Excel, but Dominic's shown you one way with VBA.

Another, non-VBA route might be to use an identically sized range that references back to the original so eg on another part of the sheet/on another sheet you have:

=A2*$A$1

and copy across and down as far as your original data requires. That way you can see your original values and also the effect of multiplying by whatever factor you place in A1.

Hope this helps!

Richard

 

[kshora]

DominicB

Thnaks a million, the macro works like magic.

Thanks once again

Shora

 

[DominicB]

Hi kshora

Alternatively, you could download John Walkenbachs's PUP add-in : it is shareware so if you're going to keep it you should pay him for it but he has included a tool on here called "Math Without Formulas". You can get a fully working evaluation version from here :

http://j-walk.com/ss/pup/pup6/index.htm

HTH

DominicB