Multiply by a range's assigned value

Question

Im trying to simply multiply two ranges by one another, and it is activated by a activeX button, but it keeps giving me the #Name? error obv from the multiplication error but i do not know how to edit that correctly. Thanks

Private Sub CommandButton1_Click()
Dim rngData     As Range
    Dim rngNum     As Range

    Set rngData = ThisWorkbook.Worksheets("Custom Systems").Range("G16")
      Set rngNum = ThisWorkbook.Worksheets("Custom Systems").Range("c8")
    rngData = Evaluate(rngData.Address & "* rngNum")
End Sub

I am not sure exactly what you are asking; Are you looking to multiply two cells to get the value? What are you trying to accomplish with `rngData = Evaluate(rngData.Address & "* rngNum")`? — PartyHatPanda, Aug 16 '16 at 16:50
`rngData = Evaluate(rngData.Address & "*" & rngNum.Address)` but @YowE3k answer is a better method. — Scott Craner, Aug 16 '16 at 16:55
@ScottCraner - VBA is weird! It appears that your suggestion works, but only if you add `.Value`, i.e. `rngData.Value = Evaluate(rngData.Add‌ress & "*" & rngNum.Address)`. — YowE3K, Aug 16 '16 at 19:48
@ScottCraner - I tried it on a single cell as well, and it still didn't work. (It doesn't crash - but it just doesn't update the value.) — YowE3K, Aug 16 '16 at 19:57

YowE3K · Answer 1 · 2016-08-16T19:46:42.867

5

The simplest solution to your question would be:

With ThisWorkbook.Worksheets("Custom Systems")
    .Range("G16").Value = .Range("G16").Value * .Range("c8").Value
End With

or

With ThisWorkbook.Worksheets("Custom Systems")
    Set RngData = .Range("G16")
    Set RngNum = .Range("c8")
    RngData.Value = RngData.Value * RngNum.Value
End With

(.Value is the default property, so that can be omitted if you want to save some characters while typing.)

If the RngData range is not a single cell, the above method won't work. In that situation I would iterate over each cell, e.g.

Dim oneCell As Range
With ThisWorkbook.Worksheets("Sheet1")
    Set rngdata = .Range("G11:G27")
    Set rngnum = .Range("C6")

    For Each oneCell In rngdata
        oneCell.Value = oneCell.Value * rngnum.Value
    Next

End With

Alternatively, you could fall back to using the built-in Paste Special Values Multiply method, e.g.

With ThisWorkbook.Worksheets("Sheet1")
    Set rngdata = .Range("G11:G27")
    Set rngnum = .Range("C6")

    rngnum.Copy
    rngdata.PasteSpecial Paste:=xlPasteValues, _
                         Operation:=xlMultiply

End With

Or you could use the Evaluate function in that situation:

With ThisWorkbook.Worksheets("Sheet1")
    Set rngdata = .Range("G11:G27")
    Set rngnum = .Range("C6")

    'Note: The ".Value" is required in the next line or it will not work
    rngdata.Value = Evaluate(rngdata.Address & "*" & rngNum.Address)
End With

Personally, I don't like the Evaluate method - I suspect it would be more inefficient than other methods.

edited Aug 16 '16 at 19:46

answered Aug 16 '16 at 16:50

YowE3K

23,852
7
26
40

using the code it keeps resulting in a mismatch error – M. Puglia Aug 16 '16 at 17:33
Private Sub Calc_Click() With ThisWorkbook.Worksheets("Custom Systems") Set rngData = .Range("G11:G27") Set rngNum = .Range("C6") rngData.Value = rngData.Value * rngNum.Value End With End Sub – M. Puglia Aug 16 '16 at 17:33
@M.Puglia - I have updated the answer to cater for multiple cells in `rngData`. – YowE3K Aug 16 '16 at 19:39
Evaluate is faster than looping on large datasets, but the pastespecial is faster than evaluate. – Scott Craner Aug 16 '16 at 19:52
@Scott Craner for me `PasteSpecial` was few times slower when I tested it with those two answers (but maybe because of the `.SpecialCells` part) http://stackoverflow.com/questions/38930863/multiply-each-value-in-a-range-by-a-constant-but-skip-blank-cells/38934067#38934067 – Slai Aug 16 '16 at 20:35
I'm learning more from this one question than I have probably learned in the last year! – YowE3K Aug 16 '16 at 20:44

score 2 · Answer 2 · answered Aug 16 '16 at 20:03

2

With ThisWorkbook.Worksheets("Custom Systems")
    .[G11:G27] = .[G11:G27*C6]
End With

answered Aug 16 '16 at 20:03

Slai

22,144
5
45
53

Cool! I assume the `[G11:G27*C6]` is just implicitly doing an Evaluate. – YowE3K Aug 16 '16 at 20:24
2

@YowE3K Yes, it's short for something like `.Range("G11:G27") = .Evaluate("G11:G27*C6")` (note the dot in front). I can't test it, but if you have time you can test how fast it is with `Timer`. I suspect it to be faster than the other alternatives. The overhead from `Evaluate` is indeed slow, but the calculation is fast. – Slai Aug 16 '16 at 20:31

Multiply by a range's assigned value

2 Answers2