Multiply selection of cells by x, whilst keeping formulas and formats, using VBA

[jaykay1]

Hi,

I constantly have to change the factor of a large series of random cells, usually by a thousand or a hundred.

I can for example

• enter "100" in a random cell,
• click copy,
• highlight all the cells I need to change,
• paste special - formulas - multiply.
• But, when doing this as frequently as I do, it takes a while!

Does anyone know how I can do this in VBA, so that I can just highlight the cells, run the macro and it will automatically paste special, formulas, multiply by say "100" for every cell?

Any help would be greatly appreciated!
Jay

 

[JoeMo]

Assuming all the cells you select have numeric constants (not formulas) in them:

Sub Multiply()
Const multiplier As Double = 100
Dim c As Range
For Each c In Selection
    c.Value = multiplier * c.Value
Next c
End Sub

 

[BiocideJ]

I believe this is what you are looking for.

A slightly modified macro from using the Macro Recorder.

Sub MultiplyRange()
    'Change C1 in the line below to whichever cell has the factor you would like to multiply by
    Range("C1").Copy
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
End Sub

 

[AlphaFrog]

Change A1 to the cell that contains the multiplier value.

[COLOR=darkblue]Sub[/COLOR] PasteSpecial_Multiply()
    [COLOR=darkblue]Dim[/COLOR] rngArea [COLOR=darkblue]As[/COLOR] Range
    Range("A1").Copy
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rngArea [COLOR=darkblue]In[/COLOR] Selection.Areas
        rngArea.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlPasteSpecialOperationMultiply
    [COLOR=darkblue]Next[/COLOR] rngArea
    Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Note: Paste Special multiply doesn't keep the formula in the destination cells.

 

[BiocideJ]

Change A1 to the cell that contains the multiplier value.

[COLOR=darkblue]Sub[/COLOR] PasteSpecial_Multiply()
    [COLOR=darkblue]Dim[/COLOR] rngArea [COLOR=darkblue]As[/COLOR] Range
    Range("A1").Copy
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rngArea [COLOR=darkblue]In[/COLOR] Selection.Areas
        rngArea.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlPasteSpecialOperationMultiply
    [COLOR=darkblue]Next[/COLOR] rngArea
    Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Note: Paste Special multiply doesn't keep the formula in the destination cells.

I submit that you don't need to iterate through the range areas for PasteSpecial operation. Apparently, Excel handles it automatically throught he PasteSpecial method.

 

[AlphaFrog]

I submit that you don't need to iterate through the range areas for PasteSpecial operation. Apparently, Excel handles it automatically throught he PasteSpecial method.

PasteSpecial throws an error for noncontinuous ranges (Cntrol+Click on two ranges). The For-Next loop iterates through the selection areas and PasteSpecial for each.

 

[jaykay1]

Thanks very much. I should have said though, i don't want to change any of the cells in the sheet, i.e. I don't want to have the put the multiplier into A1 for instance. I just want the number it's multiplied by to be a constant - always 100 (I can then manipulate the macro for 1000 as well and create another macro). I also want to keep the formulas in the cells that I select to remain the same just with x100 at the end. Is this possible??

 

[BiocideJ]

JayKay, based on your response, it seems the answer JoeMo gave is going to be your best bet.

PasteSpecial throws an error for noncontinuous ranges (Cntrol+Click on two ranges). The For-Next loop iterates through the selection areas and PasteSpecial for each.

I am using XL2010 and not having that issue. (i.e. I can run my code on Range("A1:A2,A4:A5,A7:A8"), etc). Perhaps with earlier versions. Either way, your approach will definitely work.

 

[jaykay1]

Thanks BiocideJ, it does work but the problem is that it makes the formulas constants. So if I had =sum(c1,b1) and then ran the macro on it, it would become a constant as opposed to =sum(c1,b1)*100. Sometime I have formulas and sometimes I have constants and would like them to remain intact. Any ideas?

 

[jaykay1]

Thanks everyone but someone has helped me out with this which appears to work.

Sub MultiplySelectionBy100()
Dim Cell As Range
For Each Cell In Selection
If Len(Cell.Value) > 0 And Application.IsNumber(Cell.Value) Then
If Cell.HasFormula Then
Cell.Formula = Replace(Cell.Formula, "=", "=100*(") & ")"
Else
Cell.Value = 100 * Cell.Value
End If
End If
Next
End Sub