R1C1 Formula

[Sherifa]

RUN-TIME ERROR '91'
Object variable or With block variable not set

[COLOR=#333333]Sub SummaryRow()Dim Wkb As Excel.Workbook[/COLOR]Dim ws As Worksheets
Dim ws_count As Integer
Dim i As Integer
Dim LastRow As Long




    Sheet4.Range("a183:M183").Copy
    
        Set Wkb = ThisWorkbook
        ws_count = Wkb.Worksheets.Count
                
                     
        'Begin loop
        For i = 4 To ws_count
              
       
       Set TargetRow = Wkb.Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        
        LastRow = Wkb.Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row


         'Paste Formulas
        TargetRow.PasteSpecial xlPasteFormulas
        [B]ws(i).Cells(LastRow + 1, 7).FormulaR1C1 = "=SUM(R4C7:R" & LastRow & "C7)"
        ws(i).Cells(LastRow + 1, 8).FormulaR1C1 = "=COUNT(R4C8:R" & LastRow & "C8)"
        ws(i).Cells(LastRow + 1, 9).FormulaR1C1 = "=SUM(R4C9:R" & LastRow & "C9)"[/B]
        TargetRow.PasteSpecial xlPasteFormats
        
        Next i
         [COLOR=#333333]End Sub[/COLOR]

 

[steve the fish]

I presume you meant to use:

Wkb.Worksheets(i)

rather than

ws(i)

 

[Sherifa]

I defined

Dim ws As Worksheets

so it shouldn't make a difference.
I applied the change you suggested, but it gave me the same run time error.

 

[steve the fish]

Stop the code after you have dimensioned your variables. What does ws say in the locals window? Anyway paste the code you now have.

 

[Sherifa]

: Wkb : : Workbook/ThisWorkbook
: ws : Nothing : Worksheets
: ws_count : 44 : Integer
: i : 4 : Integer
: LastRow : 0 : Long
: TargetRow : : Variant/Object/Range

 

[Sherifa]

Stop the code after you have dimensioned your variables.

I don't know what that means

 

[steve the fish]

So ws is nothing. So ws(i) cant be used. Anyway paste the code you now have once you applied what i said in the first post. I think there must be a mistake.

 

[Sherifa]

Sub SummaryRow()Dim Wkb As Excel.Workbook
Dim ws As Worksheet
Dim ws_count As Integer
Dim i As Integer
Dim LastRow As Long




    Sheet4.Range("a183:M183").Copy
    
        Set Wkb = ThisWorkbook
        ws_count = Wkb.Worksheets.Count
                
                     
        'Begin loop
        For i = 4 To ws_count
              
       
       Set TargetRow = Wkb.Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        
        LastRow = Wkb.Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row


         'Paste Formulas
        TargetRow.PasteSpecial xlPasteFormulas
        Wkb.Worksheets(i).Cells(LastRow + 1, 7).FormulaR1C1 = "=SUM(R4C7:R" & LastRow & "C7)"
        Wkb.Worksheets(i).Cells(LastRow + 1, 8).FormulaR1C1 = "=COUNTA(R4C8:R" & LastRow & "C8)"
        Wkb.Worksheets(i).Cells(LastRow + 1, 9).FormulaR1C1 = "=SUM(R4C9:R" & LastRow & "C9)"
        TargetRow.PasteSpecial xlPasteFormats
        
        Next i
        
End Sub

Thank you, why doesn't the Dim ws As Worksheet work?
The code It appears to be working now

 

[pgc01]

Why doesn't the Dim ws As Worksheet work?

I guess you mean Dim ws As Worksheets from post #1 .

The Worksheets property of the Workbook object returns a Sheets object.

If you want to use it, this is an example:

Sub Test()
Dim Wkb As Workbook
Dim ws As Sheets

        
        Set Wkb = ThisWorkbook
        Set ws = ThisWorkbook.Worksheets
        
        MsgBox ws(1).Name

End Sub

 

[Sherifa]

Thank you for all the help!