vba Excel VBA从另一列中减去一列，但不在单元格中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25188581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Subtracting one column from another, but not in a cell
提问by Vivian
Sub TryAgain()
Dim A As Range '~ I will put the result in this column, A
Dim B As Range
Dim C As Range '~ [B-C] is the what I need
Dim onecell As Range
Dim twocell As Range
Set B = Range("C2", Range("C2").End(xlDown)) 'log prices of Z over time
Set C = Range("D2", Range("D2").End(xlDown)) 'log prices of WPC over time
Set A(1) = B.Value - C.Value
End Sub
I've been struggling with solving this annoying problem for a while.
我一直在努力解决这个烦人的问题。
This is the SIMPLE subtracting operation between two columns,
这是两列之间的简单减法运算，
so what i want to do is conduct the subtract operation 'CELL-BY-CELL'
所以我想要做的是进行减法运算'CELL-BY-CELL'
BUT! I don't want to put the results in cells (to be referred) or creating any other sheets.
但！我不想将结果放在单元格中（待引用）或创建任何其他工作表。
So, I want to assign the subtraction result (i.e. C2-D2, C3-D3 ...) directly into a variable called A (this should be a column),
NOT recording on worksheet cells and referring the cells, but this is obviously very difficult to get it through.
所以，我想将减法结果（即 C2-D2、C3-D3 ...）直接分配给一个名为 A 的变量（这应该是一列），而
不是在工作表单元格上记录并引用单元格，但这显然是很难通过。
is there any other way to do this?
有没有其他方法可以做到这一点？
Much appreciated in advance!
非常感谢提前！
采纳答案by Alex
I think you are looking for an Array:
我认为您正在寻找一个Array：
Sub TryAgain()
Dim A() As Integer '~ I will put the result in this column, A
Dim i As Integer
Dim lastrow As Integer
lastrow = Range("B2").End(xlDown).Row '<- assume there's no empty cells from C2 to last row on column C
ReDim A(lastrow)
For i = 1 To lastrow
A(i) = Cells(i + 1, 2).Value - Cells(i + 1, 3).Value
Next
' add below for loop to print out result
For j = 1 To lastrow - 1
Debug.Print A(j)
Next
End Sub
回答by David
Here is a sample spreadsheet based on what you described:
以下是基于您所描述内容的示例电子表格：
And this code will solve the problem...to show it works there is a loop at the end for a message box to display the answers but you can use the arrayas shown in the code as you were planning to. I think you may want to consider the benefits of storing the data in a range but that is your design choice to make.
这段代码将解决这个问题……为了显示它的工作原理，在消息框的末尾有一个循环来显示答案，但您可以按照计划使用代码中显示的数组。我认为您可能想要考虑将数据存储在一个范围内的好处，但这是您的设计选择。
Sub SubtractCells()
Dim dblTolerance As Double
Dim tmp As Range
Dim A() As Double
Dim intListSize As Integer
Dim i As Integer
'Get source range
Set tmp = ActiveSheet.Range("C2")
'Get size of list by finding last row
Do Until tmp.Offset(1, 0).Value = ""
Set tmp = tmp.Offset(1, 0)
Loop
'Subtract last row from first row of data to get number of rows and add one for offset from beginning
intListSize = (tmp.Row - 2) + 1
ReDim A(intListSize)
i = 1 'index for array
'use the temporary variable to cycle through the range
Set tmp = ActiveSheet.Range("C2")
Do Until tmp.Value = ""
'Subtract the two rows and store in array
A(i) = tmp.Value - tmp.Offset(0, 1).Value
'Update range row and index
i = i + 1
Set tmp = tmp.Offset(1, 0)
Loop
'Loop through array to see results
'This is only to verify code works comment out and use array
'I would rethink storing this on a spreadsheet.
For i = 1 To intListSize
MsgBox ("A(" & Str(i) & "):" & Str(A(i)))
Next
'Clean up
Set tmp = Nothing
End Sub
Let me know if you have any questions.
如果您有任何问题，请告诉我。
