macro to edit formula
 

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

macro to edit formula
 

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

"Steve d'Apollonia" wrote in message
...
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

macro to edit formula
 

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

macro to edit formula
 

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


"Steve d'Apollonia" wrote in message
...
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

macro to edit formula
 

That worked, and was much simpler to do since no macro was necessary.
Thanks Tom!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!