excel - Range to Array - Insert formulas based on lookup value

Question

I am familiar with range to array subroutines. Rng = array; do something; array = Rng. I am trying to avoid a slow loop through several thousand records in a transactions database (Excel table) by putting the range into an array, then looking up a value in an adjacent column and depending on the value in that column, putting a formula into the range (array) cell. The formula varies depending on the value in the lookup column. Here is some code how I am doing it now - slowly:

Dim ws as worksheet, rng as range, LastRow as long

Set ws = Sheets("Transactions")    
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

Set rng = ws.Range("J2:J" & LastRow)   'basis

For Each cell In rngIf cell.Offset(0, 2) = "FT" Then                 'column L
    cell.Offset(0, 0) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(Multiplier,MATCH([@Project],Project,0))*[@[Plan Hrs]]"
    cell.Offset(0, 1) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(Multiplier,MATCH([@Project],Project,0))*[@[Actual Hrs]]"

    ElseIf cell.Offset(0, 2) = "PT" Then
        cell.Offset(0, 0) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(PTMult,MATCH([@Project],Project,0))*[@[Plan Hrs]]"
        cell.Offset(0, 1) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(PTMult,MATCH([@Project],Project,0))*[@[Actual Hrs]]"
    ElseIf cell.Offset(0, 2) = "Onsite" Then
        cell.Offset(0, 0) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(OnsiteM,MATCH([@Project],Project,0))*[@[Plan Hrs]]"
        cell.Offset(0, 1) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(OnsiteM,MATCH([@Project],Project,0))*[@[Actual Hrs]]"
    Else
        cell.Offset(0, 0) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(Multiplier,MATCH([@Project],Project,0))*[@[Plan Hrs]]"
        cell.Offset(0, 1) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(Multiplier,MATCH([@Project],Project,0))*[@[Actual Hrs]]"
    End If
Next cell
End Sub

What I would like to do it something like:

dim arr as variant
    arr = rng
For c = LBound(arr, 1) To UBound(arr, 1)
If Cells(c, 12) = "FT" Then
arr(c, 1) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(Multiplier,MATCH([@Project],Project,0))*[@[Plan Hrs]]"

arr(c.Offset(0, 1)) = "=IFERROR(INDEX(Roster[BASE],MATCH([@[Staff, Last Name]],Roster[LASTNAME],0)),0)*INDEX(Multiplier,MATCH([@Project],Project,0))*[@[Actual Hrs]]"
....

rng = arr

Any suggestions are welcome. And BTW the table is an Excel table. The formulas work - I just need to insert the correct formula into the cells in column J (plan hrs) and k (actual hrs) based on the value in column L (FT, PT, or Onsite). Thanks.

question from:https://stackoverflow.com/questions/66055501/range-to-array-insert-formulas-based-on-lookup-value

Answer 1

Waitting for answers