英文:
Filling every column of a Multidimensional Array with a single formula, than copy column by column to multiple Ranges at once
问题
这个问题与之前的问题相关在此处。
我有多个范围,每个范围都是一个列,比如:
"K" & firstRow & ":K" & secondRow```
而我有一个多维数组,我想用单元格公式引用填充这个数组的每一列。因此,结果应该是这样的:
``` J K
100 =J103 =K103
101 =J103 =K103
102 =J103 =K103
103 =J103 =K103
104 =J103 =K103
105 =J103 =K103
106 =J103 =K103
@pᴇʜ提出的解决方案对于单个数组完全有效。
Option Explicit
Public Sub FillFormulaUsingArray()
Dim Middle As Long
Middle = 103
Dim firstRow As Long
firstRow = 100
Dim secondRow As Long
secondRow = 106
Dim ManagAreaLength As Long
ManagAreaLength = secondRow - firstRow
Dim TmpArray() As Variant
ReDim TmpArray(1 To ManagAreaLength, 1 To 1)
Dim i As Long
For i = LBound(TmpArray, 1) To UBound(TmpArray, 1)
TmpArray(i, 1) = "=A" & Middle
Next i
Worksheets("Model").Range("B" & firstRow & ":B" & secondRow).Formula = TmpArray()
End Sub
但是我不知道如何使它适用于多个数组和范围。
我想能够像这样做:
Worksheets("Model").Range("J" & firstRow & ":J" & secondRow).formula = TmpArray(1)
Worksheets("Model").Range("K" & firstRow & ":K" & secondRow).formula = TmpArray(2)
另外,为了填充数组,我使用了以下代码:
Dim item As Variant
Dim LetterArray As Variant
LetterArray = Array("J", "K")
For j = LBound(TmpArray, 2) To UBound(TmpArray, 2)
For Each item In LetterArray
For i = LBound(TmpArray, 1) To UBound(TmpArray, 1)
TmpArray(i, j) = "=" & item & Middle
Debug.Print "i=" & i & ";j=" & j & "==" & TmpArray(i, j)
Next i
Next item
Next j
但似乎它不会产生期望的结果,因为循环在跳转到LetterArray中的另一个项时会覆盖数组。
如何以正确的方式填充数组,如何将每个数组列分配给其相应的范围?另外,如何使这个系统适应超过2个数组和范围?
英文:
This question is related to the previous question here.
I have multiple ranges, every range is a column, say:
"J" & firstRow & ":J" & secondRow
"K" & firstRow & ":K" & secondRow
And I have a multidimensional array, I want to fill each column of this array with a single cell formula reference.
So the outcome should be like:
J K
100 =J103 =K103
101 =J103 =K103
102 =J103 =K103
103 =J103 =K103
104 =J103 =K103
105 =J103 =K103
106 =J103 =K103
The solution proposed by @pᴇʜ works perfectly for a single array.
Option Explicit
Public Sub FillFormulaUsingArray()
Dim Middle As Long
Middle = 103
Dim firstRow As Long
firstRow = 100
Dim secondRow As Long
secondRow = 106
Dim ManagAreaLength As Long
ManagAreaLength = secondRow - firstRow
Dim TmpArray() As Variant
ReDim TmpArray(1 To ManagAreaLength, 1 To 1)
Dim i As Long
For i = LBound(TmpArray, 1) To UBound(TmpArray, 1)
TmpArray(i, 1) = "=A" & Middle
Next i
Worksheets("Model").Range("B" & firstRow & ":B" & secondRow).Formula = TmpArray()
End Sub
But I don't know how to make it work for multiple arrays and ranges.
I'd like to be able to do something like here:
Worksheets("Model").Range("J" & firstRow & ":J" & secondRow).formula = TmpArray(1)
Worksheets("Model").Range("K" & firstRow & ":K" & secondRow).formula = TmpArray(2)
And, to populate arrays with data, I've used the following code:
Dim item As Variant
Dim LetterArray As Variant
LetterArray = Array("J", "K")
For j = LBound(TmpArray, 2) To UBound(TmpArray, 2)
For Each item In LetterArray
For i = LBound(TmpArray, 1) To UBound(TmpArray, 1)
TmpArray(i, j) = "=" & item & Middle
Debug.Print "i=" & i & ";j=" & j & "==" & TmpArray(i, j)
Next i
Next item
Next j
But it doesn't seem to produce the desired outcome, as the loop overwrites arrays when jumping to another Item in LetterArray.
How do I populate arrays in a correct manner and how do I attribute each array column to it's corresponding range?
Also, how can I make this system adjustable to more than 2 arrays and ranges?
答案1
得分: 2
以下是代码的翻译部分:
Public Sub FillFormulaUsingArray()
Dim sLetters() As Variant: sLetters = Array("A", "C", "E")
Dim dLetters() As Variant: dLetters = Array("B", "D", "F")
Dim Middle As Long: Middle = 103
Dim firstRow As Long: firstRow = 100
Dim secondRow As Long: secondRow = 106
Dim ManagAreaLength As Long: ManagAreaLength = secondRow - firstRow + 1
Dim TmpArray() As Variant: ReDim TmpArray(1 To ManagAreaLength, 1 To 1)
Dim n As Long, i As Long
For n = LBound(sLetters) To UBound(sLetters)
For i = LBound(TmpArray, 1) To UBound(TmpArray, 1)
TmpArray(i, 1) = "=" & sLetters(n) & Middle
Next i
Worksheets("Model").Range(dLetters(n) & firstRow & ":" & dLetters(n) & secondRow).Formula = TmpArray
Next n
End Sub
英文:
Copy Formulas
<!-- language: lang-vb -->
Public Sub FillFormulaUsingArray()
Dim sLetters() As Variant: sLetters = Array("A", "C", "E")
Dim dLetters() As Variant: dLetters = Array("B", "D", "F")
Dim Middle As Long: Middle = 103
Dim firstRow As Long: firstRow = 100
Dim secondRow As Long: secondRow = 106
Dim ManagAreaLength As Long: ManagAreaLength = secondRow - firstRow + 1
Dim TmpArray() As Variant: ReDim TmpArray(1 To ManagAreaLength, 1 To 1)
Dim n As Long, i As Long
For n = LBound(sLetters) To UBound(sLetters)
For i = LBound(TmpArray, 1) To UBound(TmpArray, 1)
TmpArray(i, 1) = "=" & sLetters(n) & Middle
Next i
Worksheets("Model").Range(dLetters(n) & firstRow & ":" & dLetters(n) & secondRow).Formula = TmpArray
Next n
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论