Convert Array Formula to R1C1 style

[jwb1012]

Hello, does anyone know how to insert an array formula using R1C1 style?

I am attempting to convert the array formula (with absolute/relative references as shown below).

Sub Insert_Rows()
    Dim Sh As Worksheet
    Dim End_Row As Long
    Dim N As Long
    Dim Ins As Long
    
        For Each Sh In ActiveWorkbook.Sheets
            If Left(Sh.Name, 9) = "Labor BOE" Then
                
                End_Row = Sh.Range("T" & Rows.Count).End(xlUp).Row
                
                For N = End_Row To 3 Step -1
                    Ins = Sh.Cells(N, "A").Value
                    
                    If Ins > 0 Then
                        With Sh.Range("C" & N)
                            .FormulaArray = "=IFERROR(INDEX('Staffing Plan'!$K$14:$K$1008, MATCH(0, IF($A$1='Staffing Plan'!$W$14:$W$1008, COUNTIF($C$14:$C14, 'Staffing Plan'!$K$14:$K$1008), ""), 0)),"")"
                        End With
                    End If
                Next N
                
            End If
        Next Sh
End Sub

 

[StephenCrump]

If you're happy to combine the test for Ins>0 into the Excel formula, you can write a column of formulae without looping.

One way to do this is along these lines:

With Range("SomeRange")
    .Formula = "Your formula here"
    .FormulaArray = .FormulaR1C1
End With

In your case, it looks like the start cell is C3, so "Your formula here" is the formula that would apply for that cell.

But sorry, it's not clear from your question exactly what that is, and which bits are absolute/relative?

 

[jwb1012]

Hi Stephen - thanks for your response. The bolded/underlined cell reference below is the one causing the issue. When the summary table is copied multiple times (one below the next) this cell reference is "stuck" on $C$14.

This formula (first row of summary table) is in cell $C$15. This absolute reference to $C$14 is correct here (one cell above). But, when the second summary table is copied below, the bolded/underlined absolute cell reference is stuck on $C$14 - it should be $C$19.

FIRST SUMMARY TABLE FORMULA:

"=IFERROR(INDEX('Staffing Plan'!$K$14:$K$1008, MATCH(0, IF($A$1='Staffing Plan'!$W$14:$W$1008, COUNTIF([U][B]$C$14:[/B][/U]$C14, 'Staffing Plan'!$K$14:$K$1008), ""), 0)),"")"

SECOND SUMMARY TABLE FORMULA (INCORRECT / STUCK REFERENCE):

=IFERROR(INDEX('Staffing Plan'!$L$14:$L$1008, MATCH(0, IF($A$1='Staffing Plan'!$C$14:$C$1008, COUNTIF([U][B]$C$14:[/B][/U]$C19, 'Staffing Plan'!$L$14:$L$1008), ""), 0)),"")

SECOND SUMMARY TABLE FORMULA (CORRECT CELL REFERENCE):

"=IFERROR(INDEX('Staffing Plan'!$L$14:$L$1008, MATCH(0, IF($A$1='Staffing Plan'!$C$14:$C$1008, COUNTIF($[U][B]C$19:[/B][/U]$C19, 'Staffing Plan'!$L$14:$L$1008), ""), 0)),"")"

 

[StephenCrump]

Perhaps something like this?

lStartRow = 15  'say
lEndRow = 20

With Range("C" & lStartRow & ":C" & lEndRow)
    .Formula = "=IFERROR(INDEX('Staffing Plan'!$K$14:$K$1008,MATCH(0,IF($A$1='Staffing Plan'!$W$14:$W$1008,COUNTIF($C$" & lStartRow - 1 & ":$C" & lStartRow - 1 & ",'Staffing Plan'!$K$14:$K$1008),""""),0)),"""")"
    .FormulaArray = .FormulaR1C1
End With

 

[jwb1012]

Hi Stephen - thank you, this technique works well - I just made a slight adjustment to replace lStartRow to "N-1" - which works great.

I tried to replicate this technique below for column F, but I get a syntax error. Any thoughts on where I'm going wrong?

Sub Insert_Rows()
    Dim Sh As Worksheet
    Dim End_Row As Long
    Dim N As Long
    Dim Ins As Long
    
        For Each Sh In ActiveWorkbook.Sheets
            If Left(Sh.Name, 9) = "Labor BOE" Then
                End_Row = Sh.Range("T" & Rows.Count).End(xlUp).Row
                
                For N = End_Row To 3 Step -1
                    Ins = Sh.Cells(N, "A").Value
                    If Ins > 0 Then
                        With Sh.Cells(N, "C")
                            .Formula = "=IFERROR(INDEX('Staffing Plan'!$L$14:$L$1008, MATCH(0, IF($A$1='Staffing Plan'!$C$14:$C$1008, COUNTIF($C$" & N - 1 & ":$C" & N - 1 & ", 'Staffing Plan'!$L$14:$L$1008), """"), 0)),"""")"
                            .FormulaArray = .FormulaR1C1
                        End With
                        
                        With Sh.Cells(N, "F")
                            .Formula = "=SUM(IF('Staffing Plan'!$C$13:$C$1008=$A$1,IF('Staffing Plan'!$L$13:$L$1008=$C22,IF('Staffing Plan'!$L$13:$FV$13=[U][B]"F$" & N - 1,[/B][/U]'Staffing Plan'!$L$13:$FV$1008))))"
                            .FormulaArray = .FormulaR1C1
                        End With
                    End If
                Next N
            End If
        Next Sh
End Sub

 

[StephenCrump]

To fix the syntax error (I haven't looked at anything else):

'Change
.Formula = "=SUM(IF('Staffing Plan'!$C$13:$C$1008=$A$1,IF('Staffing Plan'!$L$13:$L$1008=$C22,IF('Staffing Plan'!$L$13:$FV$13=[COLOR=#ff0000][B]"[/B][/COLOR]F$" & N - 1,'Staffing Plan'!$L$13:$FV$1008))))"
.FormulaArray = .FormulaR1C1
'To
.FormulaArray = "=SUM(IF('Staffing Plan'!$C$13:$C$1008=$A$1,IF('Staffing Plan'!$L$13:$L$1008=$C22,IF('Staffing Plan'!$L$13:$FV$13=F$[COLOR=#ff0000][B]"[/B][/COLOR] & N - 1 [COLOR=#ff0000][B]& "[/B][/COLOR],'Staffing Plan'!$L$13:$FV$1008))))"

(If you write cell by cell, or if you write to a range of cells with one array formula, as opposed to a range with individual array formulae in each cell, you can go straight to .FormulaArray in one step)