乘法阵列，标量和VBA加入 [英] Multiplying arrays with scalars and adding in VBA

问题描述

我有一个Excel工作表是大小相等，都只能包含数字两列数据。

我试图写一个宏，这将使这两个组的成数组，然后对他们进行计算。具体来说 ArrayA + 3 * ArrayB 然后把结果返回到工作表中的新列。下面是code我有这么远。

 暗淡ArrayA为Variant
昏暗ArrayB为Variant
昏暗ArrayC为VariantArrayA =工作表（工作表Sheet1）的范围。（A1：A5）
ArrayB =工作表（工作表Sheet1），范围（B1：B5）。这是哪里的东西变坏
ArrayC = ArrayA + 3 * ArrayB
 

解决方案

您需要先值的数组。然后确保找你elooping htrough数组。

 暗淡ArrayA为Variant
昏暗ArrayB为Variant
昏暗ArrayC（）为VariantArrayA =工作表（工作表Sheet1）范围。（A1：A5）值
ArrayB =工作表（工作表Sheet1），范围（B1：B5）。价值这是哪里的东西变坏
'ArrayC = ArrayA + 3 * ArrayB手动进行列C大小相同数组A
使用ReDim ArrayC（LBOUND（ArrayA，1）向UBound函数（ArrayA，1），1：1）昏暗我只要'变量来计算，我们在其上阵列的部分循环通过每个点的诠释，他阵列和执行运算
对于i = LBOUND（ArrayA，1）UBound函数（ArrayA，1）
    ArrayC（I，1）= ArrayA（I，1）+ 3 * ArrayB（I，1）
接下来，我工作表（工作表Sheet1），范围（C1：C5）。值= ArrayC。
 

注意上面code是未经检验的，所以我可以在里面有一个错字的地方。此外，您可能需要使用REDIM时宣布ArrayC的数据类型，而不是变异。我当时无法测试因为我从我的手机接听。

I have two columns of data in an excel sheet that are equal in size and both only contain numbers.

I'm trying to write a macros which will put both of these sets into arrays, then perform a calculation on them. Specifically ArrayA + 3*ArrayB then put the result back into the worksheet in a new column. Below is the code I have so far.

Dim ArrayA As Variant
Dim ArrayB As Variant
Dim ArrayC As Variant

ArrayA = Worksheets("Sheet1").Range("A1:A5")
ArrayB = Worksheets("Sheet1").Range("B1:B5")

'this is where things go bad
ArrayC = ArrayA + 3 * ArrayB

解决方案

YOu need to first make an array of values. Then be sure that you'r elooping htrough the arrays.

Dim ArrayA As Variant
Dim ArrayB As Variant
Dim ArrayC() As Variant

ArrayA = Worksheets("Sheet1").Range("A1:A5").Value
ArrayB = Worksheets("Sheet1").Range("B1:B5").Value

'this is where things go bad
'ArrayC = ArrayA + 3 * ArrayB

'manually make array C the same size as array A
ReDim ArrayC(LBound(ArrayA, 1) To UBound(ArrayA, 1), 1 To 1)

Dim i As Long 'variable to count which section of the array we are on

' loop through each spot int he array and perform the math
For i = LBound(ArrayA, 1) To UBound(ArrayA, 1)
    ArrayC(i, 1) = ArrayA(i, 1) + 3 * ArrayB(i, 1)
Next i

Worksheets("Sheet1").Range("C1:C5").Value = ArrayC

Note The above code is untested so I may have a typo in it somewhere. Also, you may need to declare ArrayC's data type instead of variant when using redim. I am unable to test at the moment as i'm answering from my mobile phone.

这篇关于乘法阵列，标量和VBA加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！