2
Sub Test()

    Dim rngData As Range

    Set rngData = ThisWorkbook.Worksheets.ActiveCell
    rngData = Evaluate(rngData.Address & "*2")
End Sub

I am trying to multiple the entire sheet by 2, yet I am getting an error. Any help would be highly appreciated.

Thanks Hayk

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Hayk
  • 47
  • 2

3 Answers3

2

The below will multiply the ActiveCell by two. Note that this does not multiply the entire sheet!

Sub Test()
    Dim rngData As Range
    Set rngData = ActiveCell
    rngData = Evaluate(rngData * 2)
End Sub

If you truly want to multiply all values in the worksheet by 2, I would look in to looping through a range.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • @dwirony - `ThisWorkbook.Worksheets("Sheet1").ActiveCell` throws an error. Is that because `ActiveCell` implicitly already includes `ThisWorkbook.Worksheets("Sheet1")` (assuming `Sheet1` is the activesheet?) – BruceWayne Dec 21 '18 at 20:40
2

Give this a try:

Sub MultiplyByTwo()
    Dim r As Range, rng As Range

    On Error Resume Next
        Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
    On Error GoTo 0

    If rng Is Nothing Then Exit Sub

    For Each r In rng
        r.Value = 2 * r.Value
    Next r
End Sub

It multiplies all numeric constant cells in the worksheet by 2. It will ignore:

  1. empty cells
  2. text cells
  3. formula cells
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    Since this runs over an entire spreadsheet, would turning calculation to manual, and screen updating false at the start help performance in any measurable way? – BruceWayne Dec 23 '18 at 23:16
0

Here's a slightly less conventional way to approach this, but you could use the Paste Special... > Multiply feature. E.g., for this example, I placed a 2 in cell A17, and applied the multiplier to my A1:F15 range. Here's what that looks like programmatically:

Sub MultiplyAll()
    Range("A17").Select 'select the multiplier from cell A17
    Application.CutCopyMode = False
    Selection.Copy 'capture the multiplier "2" onto the clipboard
    Range("A1:F15").Select 'select the range that will be multiplied
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
End Sub

This might not be a great approach for your use case, but consider that you might not even need a macro if you just need to paste your multiplier onto a range manually.

Marc
  • 11,403
  • 2
  • 35
  • 45