0

I am very new to VBA and I am seeking help to solve the following problem. I need to multiply a range by a single number. All of the cells with in that range need to be multiplied by that one number.

Thank you!

Community
  • 1
  • 1
Juanito
  • 9
  • 1
  • 2

2 Answers2

1

As what I comment just now.
1.Enter the formula at the cells (e.g. G1)
Formula
2.Press Enter key and drag the formula
enter image description here

keong kenshih
  • 524
  • 3
  • 8
1

keong has already shown you one method but unfortunately that method requires one to do the calculation in another cell/range. If that is what you want then go with keong's answer but if you want to do the calculation in the same range then continue reading below.

Here is another method which doesn't use formulas or VBA.

Let's say the range is A1:A10 and you want to multiply the entire range by 5

  1. Simply type 5 in any blank cell. You can delete that later.
  2. Copy that cell
  3. Select Range A1:A10
  4. Right click on it
  5. Click on Paste Special | Values - Multiply as shown below and you are done.

Before

enter image description here

After

enter image description here

Followup from comments

In case you do not want to use a temp cell to write 5 then you can directly set 5 in the clipboard and use it like this.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim MyData As New DataObject
    Dim rng As Range

    Set ws = Sheet1
    Set rng = ws.Range("A1:A5")

    '~~> Put the number 5 in clipboard
    MyData.SetText 5
    MyData.PutInClipboard

    '~~> Get the data from clipboard
    MyData.GetFromClipboard

    rng.Formula = Application.Evaluate("=" & _
                                    rng.Address & _
                                    "*" & _
                                    Val(MyData.GetText))
End Sub

Like I mentioned, you don't need VBA for this but if you still want to use VBA then you can use this instead of copying the data to the clipboard.

rng.Formula = Application.Evaluate("=" & rng.Address & "*" & MYNUMBER)

Where MYNUMBER is the variable which has the number that you want to multiply the range with.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Hey, Sid, can you think of a way to do this in VBA without using a cell as the source value? What I don't know how to do is put something in the Office clipboard. And it doesn't look like you can PasteSpecial from the Windows clipboard. – Doug Glancy Jul 02 '15 at 03:20
  • @DougGlancy: See the answer by i_saw_drones [Here](http://stackoverflow.com/questions/10336805/vb-excel-pastespecial-requiring-clipboard-content) Since the data is stored as text, the `Paste Special | Values - Multiply` is not available. However `PasteSpecial Format:="Text"` is available which doesn't support `Multiply` I can always create a new hidden sheet and type the number there and then copy it? Once the multiplication is done, one can always delete the sheet. – Siddharth Rout Jul 02 '15 at 03:40
  • Siddharth, yup the hidden sheet or some such is a way to go. But if it comes to that I think I'd just loop through an array instead. Just wondered if there was a way just in VBA. Nice talking to you! – Doug Glancy Jul 02 '15 at 03:43
  • There is an alternative if you want to use the clipboard. I just updated my post. – Siddharth Rout Jul 02 '15 at 03:50
  • There you go! I'll upvote you again. – Doug Glancy Jul 02 '15 at 03:52
  • But honestly why put the data in clipboard in the first place? The idea was not to use VBA. In VBA, I can directly multiply the range by 5. – Siddharth Rout Jul 02 '15 at 03:53
  • `rng.Formula = Application.Evaluate("=" & rng.Address & "*" & 5)` – Siddharth Rout Jul 02 '15 at 03:54
  • That was your idea. Mine was to goof around and try something new :) – Doug Glancy Jul 02 '15 at 04:26
  • lol@DougGlancy Very True – Siddharth Rout Jul 02 '15 at 04:35