Excel VBA如何将范围乘以一个数字 [英] Excel VBA How to multiply range by one number

问题描述

我是VBA的新手，我正在寻求帮助来解决以下问题.我需要将范围乘以一个数字.所有具有该范围的像元都需要乘以一个数字.

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.

谢谢！

推荐答案

keong已经向您展示了一种方法，但不幸的是，该方法需要一个方法才能在另一个单元格/范围中进行计算.如果那是您想要的，请遵循keong的答案，但是如果您要在相同范围内进行计算，请继续阅读下面的内容.

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.

这是另一种不使用公式或VBA的方法.

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

假设范围是 A1:A10 ，而您想将整个范围乘以 5

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

1. 只需在任何空白单元格中键入 5 .您可以稍后将其删除.
2. 复制该单元格
3. 选择范围 A1:A10
4. 右键单击它
5. 单击 Paste Special |值-乘以，如下所示，您就完成了.

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.

之前

之后

评论的关注

如果您不想使用临时单元格写入5，则可以直接在剪贴板中设置 5 并像这样使用它.

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

就像我提到的那样，您不需要VBA，但是如果您仍然想使用VBA，则可以使用它代替将数据复制到剪贴板.

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)

其中 MYNUMBER 是具有要与范围相乘的数字的变量.

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

这篇关于Excel VBA如何将范围乘以一个数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！