Filling every column of a Multidimensional Array with a single formula, than copy column by column to multiple Ranges at once

Question

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?

Answer 1

Copy Formulas

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