Insertar los valores de celdas en un arreglo

Necesito crear una macro que inserte los valores de varias celdas con funciones en un arreglo. Poseo esas dos subrutinas, pero no sé cómo unir las ideas

Sub macro1()
f = Range("AG5").CurrentRegion.Rows.Count
  Range("AQ5").Resize(f, 1).FormulaR1C1 = "=IFERROR(IF(RC35=""Y"",VLOOKUP(RC33,Hoja2!C2:C5,4,0)*RC24,""""),"""")"
    Range("AR5").Resize(f, 1).FormulaR1C1 = "=IFERROR(IF(RC35=""Y"",VLOOKUP(RC33,Hoja2!C2:C6,5,0)*RC24,""""),"""")"
    Range("AS5").Resize(f, 1).FormulaR1C1 = "=IFERROR(IF(RC35=""Y"",VLOOKUP(RC33,Hoja2!C2:C7,6,0)*RC24,""""),"""")"
    Range("AT5").Resize(f, 1).FormulaR1C1 = "=IFERROR(IF(RC35=""Y"",VLOOKUP(RC33,Hoja2!C2:C8,7,0)*RC24,""""),"""")"
    Range("AU5").Resize(f, 1).FormulaR1C1 = "=IFERROR(IF(RC35=""Y"",VLOOKUP(RC33,Hoja2!C2:C9,8,0)*RC24,""""),"""")"
    Range("AV5").Resize(f, 1).FormulaR1C1 = "=IF(RC35=""Y"",SUM(RC43:RC46)-RC24,"""")"
End Sub
Sub Ciclo2()
    Dim nFilas As Long, nColumnas As Long
    Dim Fila As Long, Columna As Long
    Dim Valor As Long
    Dim Rango As Range
    Dim vArray() As Long
    Dim IniciarTiempo As Double
    nFilas = [nF]
    nColumnas = [nC]
    IniciarTiempo = Timer
    ReDim vArray(1 To nFilas, 1 To nColumnas)
    Set Rango = Range(Cells(1, 1), Cells(nFilas, nColumnas))
    Valor = 1
    Application.ScreenUpdating = False
    For Fila = 1 To nFilas
        For Columna = 1 To nColumnas
            vArray(Fila, Columna) = Valor
            Valor = Valor + 1
        Next Columna
    Next Fila
    Rango.Value = vArray
    Application.ScreenUpdating = True
    MsgBox Format(Timer - IniciarTiempo, "00.00")
End Sub