VBA R1C1 notation Loop reference mismatch

Question

I have a Macro using R1C1 notation that loops over data and produces the difference between the estimated data and actual. I want to be able to produce the percentage difference but I am unable to pick up the reference. My code is:

     Worksheets("IPT Bill Lab").Cells(intLoopCount, 17).FormulaR1C1 = "=R" & intLoopCount & "(C9/R" & intLoopCount & "C6,)-1"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 17).NumberFormat = "%0.00"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 17).Select

starting on the 5th row.

The first data point my excel sheet gives me is =$5:$5($I:$I/$F$5,)-1 but I'm sure I want =$5:$5($I:$5/$F$5,)-1.

These column all display #REF!. How do I fix this?

Here's the Sub in question.

   Sub AddBLabEstimationFields(DataPresent As Boolean)

    ' Add estimation fields to BL Sheet

   Dim intStartRow As Integer
   Dim intStartCol As Integer
   Dim intEndRow As Integer
   Dim intEndCol As Integer
   Dim intLoopCount As Integer
   Dim strSelectionName As String
   Dim strFormulaString As String
   '

Worksheets("IPT Bill Lab").Select
Worksheets("IPT Bill Lab").Range("A1").Select

Cells.Find(What:="Grand Total", After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

'row above is last row we want
intEndRow = ActiveCell.Row - 1

'clear old data and formulae
intStartRow = 5
intEndCol = 20
intStartCol = 4
Worksheets("IPT Bill Lab").Range(Cells(intStartRow, intStartCol), Cells(100, intEndCol)).Select
Selection.Activate
Selection.Delete

'ActiveSheet.Shapes.Range(Array("NoBillLabour")).Select
If DataPresent Then

    If intEndRow > 4 Then 'there is data
        ActiveSheet.Shapes.Range(Array("NoDataValues")).Visible = msoFalse

        'Set names on totals row....
        strFormulaString = "='IPT Bill Lab'!R" & (intEndRow + 1) & "C2"
        ActiveWorkbook.Names.Add Name:="LabBillHours", RefersToR1C1:=strFormulaString

        strFormulaString = "='IPT Bill Lab'!R" & (intEndRow + 1) & "C3"
        ActiveWorkbook.Names.Add Name:="LabBillCost", RefersToR1C1:=strFormulaString

        'loop thru adding new formulae
        For intLoopCount = intStartRow To intEndRow
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 4).FormulaR1C1 = "=R" & intLoopCount & "C3/R" & intLoopCount & "C2"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 4).NumberFormat = "0.0000"

            '"=R" & intLoopCount & "C3/BL_WeeksInMonth*(BL_WeeksInMonth-BL_WeeksOfData)"
            'estimated hours
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 6).FormulaR1C1 = "=R" & intLoopCount & "C2/BLab_WeeksOfData*(BLab_WeeksInMonth-BLab_WeeksOfData)"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 6).NumberFormat = "0.00"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 6).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 13434879
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With

            'estimated costs
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 7).FormulaR1C1 = "=R" & intLoopCount & "C6*R" & intLoopCount & "C4"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 7).NumberFormat = "$#,##0.00"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 7).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 13434879
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With

            'IPT Hours
            'Worksheets("IPT Bill Lab").Cells(intLoopCount, 9).FormulaR1C1 = "=R" & intLoopCount & "C6+R" & intLoopCount & "C2"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 9).FormulaR1C1 = "=R" & intLoopCount & "C2"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 9).NumberFormat = "0.00"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 9).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 5296274
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With

            'IPT costs
            'Worksheets("IPT Bill Lab").Cells(intLoopCount, 10).FormulaR1C1 = "=R" & intLoopCount & "C7+R" & intLoopCount & "C3"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 10).FormulaR1C1 = "=R" & intLoopCount & "C3"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 10).NumberFormat = "$#,##0.00"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 10).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 5296274
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
                            '=LEN(A5)-FIND("@",SUBSTITUTE(A5," ","@",LEN(A5)-LEN(SUBSTITUTE(A5," ",""))))+1
            strFormulaString = "=LEN(R" & intLoopCount & "C1)-FIND(""@"",SUBSTITUTE(R" & intLoopCount & "C1,"" "",""@"",LEN(R" & intLoopCount & "C1)-LEN(SUBSTITUTE(R" & intLoopCount & "C1,"" "",""""))))+1"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 12).FormulaR1C1 = strFormulaString

            '=RIGHT(A5,L5-1)
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 13).FormulaR1C1 = "=RIGHT(R" & intLoopCount & "C1,R" & intLoopCount & "C12-1)"

            '=LEFT(A5,LEN(A5)-L5)
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 14).FormulaR1C1 = "=LEFT(R" & intLoopCount & "C1,LEN(R" & intLoopCount & "C1)-R" & intLoopCount & "C12)"

            '=$I5-$F5


            Worksheets("IPT Bill Lab").Cells(intLoopCount, 16).FormulaR1C1 = "=R" & intLoopCount & "C9-R" & intLoopCount & "C6"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 16).NumberFormat = "0.00"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 16).Select

            '=($I5-$F5)-1 percentage hours
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 17).FormulaR1C1 = "=R" & intLoopCount & "(C9/R" & intLoopCount & "C6,)-1"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 17).NumberFormat = "%0.00"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 17).Select

            '=$J5-$G5

            Worksheets("IPT Bill Lab").Cells(intLoopCount, 18).FormulaR1C1 = "=R" & intLoopCount & "C10-R" & intLoopCount & "C7"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 18).NumberFormat = "$#,##0.00"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 18).Select

            '=($J5-$G5)-1 percentage change cost
                                                                            '"=R" & intLoopCount & "C3/R" & intLoopCount & "C2"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 19).FormulaR1C1 = "=R" & intLoopCount & "(C10/R" & intLoopCount & "C7)-1"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 19).NumberFormat = "%0.00"
            Worksheets("IPT Bill Lab").Cells(intLoopCount, 19).Select

        Next intLoopCount

Welcome to the site! Check out the [tour](https://stackoverflow.com/tour) and the [how-to-ask page](https://stackoverflow.com/help/how-to-ask) for more about asking questions that will attract quality answers. You can [edit your question](https://stackoverflow.com/posts/52152101/edit) to include more information. — cxw, Sep 03 '18 at 14:55

score 0 · Answer 1 · answered Sep 03 '18 at 15:03

That formula doesn't make sense as written. I think you meant this:

With Worksheets("IPT Bill Lab").Cells(intLoopCount, 17)
    .FormulaR1C1 = "=(RC9/RC6)-1"
    .NumberFormat = "0.00%"
    With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 5296274
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
End With

Note that using R without a row number means a reference to the same row the formula is on.

VBA R1C1 notation Loop reference mismatch

1 Answers1