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

"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

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!

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!

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