CODEWITHSUNDEEP
excelvba
Hayk
Hayk

Reputation: 47

Multiplying the Entire Sheet by 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

Upvotes: 2

Views: 140

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 2

Marc
Marc

Reputation: 11613

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.

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

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.

Upvotes: 2

Related Questions