VBA Excel 将范围存储为数组，提取公式的单元格值。其他变量的偏移量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14626883/
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
VBA Excel Store Range as Array, extract cell values for formula. Offset for other variables
提问by user2027945
I'm a bit new at this. How would I take the column and put the cell data of which is an integer and go through all values in that range to put it into a function to output the result into another column in the excel workbook. So my output column will be the entire Comm column using columns G, J and K for inputs into the function =100000*slotNumber+300*xpos+ypos
我对此有点陌生。我将如何获取该列并将其单元格数据放入整数并遍历该范围内的所有值以将其放入一个函数中以将结果输出到excel工作簿中的另一列中。所以我的输出列将是整个 Comm 列，使用列 G、J 和 K 作为函数的输入=100000*slotNumber+300*xpos+ypos
A B C D E F G H I J K
1 Proc Equip Operat Shift Comm Casette SlotNumber Diam Measure XPos YPos
2
3'
So thought if I took the values of each and made a for loop I could take the values and somehow do all this, just not sure how! Please and thank you!
所以想如果我获取每个值并创建一个 for 循环，我可以获取这些值并以某种方式完成所有这些，只是不确定如何！谢谢，麻烦您了！
EDIT: I have all columns stored, now I must pass the Array values into the function one by one, for the formula Z = 100000*slotArr(i)+300xList(i)+yList(i)or maybe I can just place it in the for loop.
编辑：我存储了所有列，现在我必须将数组值一一传递给函数，用于公式，Z = 100000*slotArr(i)+300xList(i)+yList(i)或者我可以将它放在 for 循环中。
EDIT: Having placed the function in the loop...I am getting an object out of range error...at the line of the function.
编辑：将函数放入循环中......我得到一个对象超出范围错误......在函数的行中。
Sub cmdMeans_Click()
Dim i As Long, j As Long
Dim slotList As Range, slotArr() As Variant, xList As Range, xArr() As Variant
Dim yList As Range, yArr() As Variant, cArr() As Variant
Set slotList = Range("P2", Range("P2").End(xlDown))
slotArr() = slotList.Value
Set xList = slotList.Offset(0, 4)
xArr() = xList.Value
Set yList = slotList.Offset(0, 5)
yArr() = yList.Value
'Only one counter required because of the dependancy on the range slotList
For i = 2 To UBound(slotArr, 1)
'Dimensioning Array
ReDim cArr(UBound(slotArr, 1), 1)
cArr(i, 1) = (100000 * slotArr(i, 1)) + (300 * xList(i, 1)) + yList(i, 1)
'MsgBox ("Comment Cell Value" & cArr(i, 1))
Next
'Resizing Array
ReDim Preserve cArr(i)
'This is where the new values will be written to the comment column
Dim cRng As Range
Set cRng = Range(Cells(14, 1), Cells(UBound(cArr(i))))
cRng.Value = Application.Transpose(cArr)
End Sub
回答by bonCodigo
I get worried to look at your sample - appolgy but really not decipherable... So I stick with your question title and comment:
我很担心看你的样本 - appolgy 但真的不是decipherable......所以我坚持你的问题标题和评论：
VBA Excel Store Range as Array, extract cell values for formula. Offset for other variables.
VBA Excel Store Range as Array, extract cell values for formula. Offset for other variables.
How store Range as Array:-
如何将范围存储为数组：-
Dim vArray as Variant
vArray = Sheets(1).Range("A2:G50").Value)
How to pass array into a function that takes an array as a parameter and returns an array:-
如何将数组传递给以数组为参数并返回数组的函数：-
Function passArray(ByRef vA as Variant) as Variant
Dim myProcessedArray as Variant
'----your code goes here
passArray = myProcessedArray
End Function
Output Single Dimensional array to worksheet Range:-
输出一维数组到工作表范围：-
Sheets(1).Range("E2").Resize(1, _
UBound(Application.Transpose(singleDArray))) = singleDArray
Output Multi Dimensional array to worksheet Range:-
将多维数组输出到工作表范围：-
Sheets(1).Range("E2").Resize(UBound(multiDArray) + 1, _
UBound(Application.Transpose(multiDArray))) = multiDArray
