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

[jaykay1]

Hi,

I tried to post this before and everyone was very helpful but don't think i was clear enough in my explanation, so thought it easier to start again.

I constantly have to change the factor of a large series of random cells by 100.

To do this, I usually

• 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 <acronym title="visual basic for applications">VBA</acronym>, so that I can just highlight the cells, run the macro and it will automatically paste special, formulas, multiply by "100" for every cell?

A few added complications -

• I don't want to change any of the cells in the workbook and do not want to have to type in 100 into any cell, I want this to be contained in the VBA.
• Also, some of the cells I highlight will contain formulas which I would like to remain intact, just with "x100" at the end. i.e. =sum(c1,d1) will become =sum(c1,d1)x100
• Finally the cells I highlight are usually scattered all over the place and aren't just one continuous region/array.

Any help would be greatly appreciated!
Jay
​

 

[Rick Rothstein]

Give this macro a try...

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

 

[jaykay1]

That's great but unfortunately I think that only works for those where I have formulas, sometimes I have constants... any ideas?

 

[Rick Rothstein]

That's great but unfortunately I think that only works for those where I have formulas, sometimes I have constants... any ideas?

Did you try it?

 

[jaykay1]

Apologies that does actually work! Fantastic, thanks very much!!