VBA Array to read in a table, enter in formulas, then pass back to sheet

[sshone1]

Hello,

I am trying to speed up a macro by passing a range to an array, loop through and add formulas to cells (uses a sheet name that changes), then pass back to the range.... I have it working without an array but it runs very slow.... The range that has 1700 rows takes almost an hour to loop through...

Dim arrCSS() As Long
Dim arr_CSS As Variant
Dim Y_CSS As Integer
Dim Y As Long
Dim copyWs As Worksheet
Dim Testsheet As Worksheet
Dim cName As String
' Application.Calculation = xlManual

Set copyWs = Sheets("Cost Summary")
Y_CSS = copyWs.Range("B" & Rows.count).End(xlUp).Row
arr_CSS = Range("B10:BN" & Y_CSS).Value

For Y = 10 To UBound(arrCSS)
cName = copyWs.Range("B" & Y)
copyWs.Range("AF" & Y).Formula = "=IF('" & cName & "'!U5=0,"""",'" & cName & "'!U5)" 'Data Management
copyWs.Range("AG" & Y).Formula = "=IF('" & cName & "'!V5=0,"""",'" & cName & "'!V5)" 'Lead Engineer

'There are 20 more of these statements to add more formulas, took them out to save space

Next Y

Range("B10:BN" & Y_CSS).Value = arr_CSS

I believe that I am passing the range to the array, I just do not understand how to manipulate the array...

cName variable in the original code gets the cell value from the first row and uses it as a string to help make the unique formulas...

Any help on this would be great, thank you!

Steve

 

[GWteB]

Not sure what exactly you're trying to do because you're copying a range from column B:B through BN:BN into an Array memory type. After that, within a loop which runs x times (based on number of columns minus 10), you're modifying the formulas of columns AF:AG based on data in column B:B solely, so based on number of rows. The latter may differ from the number of columns.
Try the code below on a copy of your worksheet and watch what happens. It might set you on the right track.

Sub sshone1()

    Dim arrIN   As Variant
    Dim arrAF   As Variant
    Dim arrAG   As Variant
    Dim oWs     As Worksheet
    Dim r       As Long
    
    Set oWs = ThisWorkbook.Sheets("Cost Summary")           ' <<< change to suit

    With oWs
        arrIN = .Range("B10:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
    End With
    arrAF = arrIN
    arrAG = arrIN

    For r = LBound(arrIN) To UBound(arrIN)
        arrAF(r, 1) = "=IF('" & arrIN(r, 1) & "'!U5=0,"""",'" & arrIN(r, 1) & "'!U5)"
        arrAG(r, 1) = "=IF('" & arrIN(r, 1) & "'!V5=0,"""",'" & arrIN(r, 1) & "'!V5)"
    Next r
    
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    
    oWs.Range("AF10").Resize(UBound(arrIN)).Formula = arrAF
    oWs.Range("AG10").Resize(UBound(arrIN)).Formula = arrAG
    
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
End Sub