How do I paste a formula into a cell based on a cell values coming from an array?

Question

I'm going to try to simplify this a bit because I don't have any code written for this portion of my macro. My macro takes data and copy pastes it multiple times depending on how many time periods I want. The next step is it takes the values in an array and pastes it in a column next to the data the same amount of times. I'm trying to figure out how to then paste formulas into the next column based on which value in the array is in that cell.

Columns A-Q (data) Column R (pasted values from array, 17 total integers) Column S (paste formulas based on value in column R). Note this data extends down between 150k-250k rows so something efficient is desired if possible. I feel like a loop would take years.

Would this just be a two dimensional array? or some sort of vlookup coded in vba? Just looking for a bump in the right direction here. Thanks.

Answer 1

If I understood you correctly, you have a data array (located in columns A-R), and column R is the only thing the formula in column S is directly dependent on.

This macro should do the trick. Assuming you don't plan to do anything computationally intensive to determine the formula, it will also be very fast. On my computer, these two as they are written written execute in 1-2 seconds on 1 million rows.

The macro assumes you will select the part of the data table you want to process. It is only important it includes your "column R" as the last column. It would be also easy to rewrite it so it always takes column R, and it figures out on its own how many rows they are, in case that's your desired behavior.

The macro:

Sub MyMacro()

Dim DataRange As Excel.Range
Dim InputRange As Excel.Range
Dim OutputRange As Excel.Range

Dim InputData() As Variant
Dim OutputData() As Variant

Dim RowNumber As Long
Dim ColumnNumber As Long

Dim i As Long

Set DataRange = Excel.Selection

RowNumber = DataRange.Rows.Count
ColumnNumber = DataRange.Columns.Count

'this sets the input as the last column in the data range and
'the output range exactly next to your array of inputs,
'regardless of whether it ends on column R, S, or wherever
Set InputRange = DataRange.Columns(ColumnNumber)
Set OutputRange = InputRange.Offset(0, 1)

'this stores the values from the input range into an array, 
'and initializes the output array (it will be a 2D array - (Row, Column)    
InputData = InputRange.Value
OutputData = OutputRange.Value

'you populate the value array inside VBA and only output to excel once -
'this is MUCH faster than interating through the cells directly
For i = 1 To RowNumber
    OutputData(i, 1) = FunctionThatReturnsFormulasFromInputs(InputData(i, 1))
Next i

OutputRange.Formula = OutputData

End Sub

Example function:

Function FunctionThatReturnsFormulasFromInputs(InputValue As Variant) As String

Select Case InputValue
    Case 1
        FunctionThatReturnsFormulasFromInputs = "aaa"
    Case Else
        FunctionThatReturnsFormulasFromInputs = "bbb"
End Select

End Function