macro to edit formula

  • Thread starter Thread starter Steve d'Apollonia
  • Start date Start date
S

Steve d'Apollonia

I've been trying, without luck, to get a macro to work that will append a
value to a formula that references a range of cells in my worksheet.

The formula simply copies values from another worksheet into a new
worksheet. I need to multiply the values in a given range of cells in the
new worksheet by a number. I've been doing this manually by selecting a
cell, then editing the formula by hitting Ctrl-V to append the number in the
formula, then I select the next cell, and repeat the edit. I have to do this
hundreds of time.

I know there's a simple way of having a macro do this, but nothing try
seems to work.

Any suggestions would be very much appreciated.

Steve
 
Steve,
I used the PasteSpecial Multiply function in the following code.

Range("B1") = InputBox("Enter multiplier", "Multiplier")
Range("B1").Copy
Range("A1:A5").PasteSpecial Paste:=xlAll, Operation:=xlMultiply,
SkipBlanks:= _
False, Transpose:=False
Range("B1").ClearContents

B1 can be any cell away from your data. A1:A5 can be modified to any range
you want.

see if this works for you...

sb
 
thanks sb...your code almost worked. When I tried to run it, it came
back with the error: "This operation requires the merged cells to be
identically sized."

As a workaround I tried making the multiplier cells away from the data
with the same range as the range of the data defined in the macro. But I
still got the same error message above.

sd
 
in a blank cell, put the number

then select that cell and do Edit=>Copy

no select your cells with the formula

do Edit=>PasteSpecial=>Values and Multiply

assume the number is 6

if a cell contained the formula

=Sheet1!A1

after the operation it will contain

=(Sheet1!A1)*6


Regards,
Tom Ogilvy
 
Back
Top