Object Required Compile Error as part of a For Each Loop

[SBF12345]

Greetings,

I am receiving an "object required" compile error on the line below.

Set R1 = wsPA.Cells(G, "A").Row

I thought .Row was an object; its a property

Basically what I would like to do is loop through a range of rows defined by two integer variables, in the case of "R1" G and I as defined by the Range "CC"

Can I maintain this loop structure in similar form using the row property as a basis for the step in the loop?

Below is the entire script to put the R1 problem into context.

Sub TRENDSHEET()

Dim wsPA As Worksheet
Dim wsTA As Worksheet
Dim PA As Workbook
Dim TA As Workbook
Dim A As Integer
Dim B As Integer 
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim G As Integer
Dim H As Integer
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim M As Integer
Dim N As Integer
Dim AA As Range
Dim BB As Range
Dim CC As Range
Dim DD As Range
Dim R1 As Range
Dim R2 As Range








Set TA = Workbooks("COMM_TREND.xls")
Set PA = Workbooks("COMM_PA.xls")








For Each wsPA In PA.Worksheets


Set wsTA = TA.Worksheets(wsPA.Name)


A = wsPA.Cells(Rows.Count, "A").End(xlUp).Row


    Do Until A = 2
      
       
        If wsPA.Cells(A, "E").Value > Application.Large(wsPA.Cells((A - 10), "E").Resize(21), 2) Then
       
        
       
        Set AA = wsPA.Cells(A, "E")
        Set DD = wsPA.Cells(A, "A")
        G = AA.Row
        H = wsPA.Cells(A, "A").Row
    
        wsTA.Activate
        
        Range("A1").Select
        Selection.End(xlDown).Offset(1, 0).Select
        Selection.Value = "ACTIVE"
            
        Range("A1").Select
        Selection.End(xlDown).Offset(0, 1).Select
        Selection.Value = DD.Value
        
        Range("A1").End(xlDown).Offset(0, 2).Select
        Selection.Value = AA.Value
       
        
        A = E
   
    Do Until E - 10 = 2
                      
        I = 2
                      
       
        Set CC = wsPA.Range(wsPA.Cells(G, "A"), wsPA.Cells(I, "A"))
             
        D = E - 10
        
       
        Set R1 = wsPA.Cells(G, "A").Row
        
            For Each R1 In CC
            
          
            H = wsPA.Range(wsPA.Cells(E, "A")).Row
           
            BB = wsPA.Range(wsPA.Cells(G, "A"), wsPA.Cells(I, "A")
            
           
            Set R2 = wsPA.Cells(G, "A").Row
            
                For Each R2 In BB
        
               
                B = (wsPA.Cells(D, "E").Value - wsPA.Cells(G, "E").Value) / (wsPA.Cells(D, "A").Value - wsPA.Cells(G, "A").Value)
    
               
                C = wsPA.Range(wsPA.Cells(D, "A"), wsPA.Cells(G, "A")).Rows.Count
                
             
                I = (wsPA.Cells(D, "E").Value - wsPA.Cells(G, "E").Value)
        
     
                
                F = A - (A - H)
            
                J = wsPA.Range(wsPA.Cells(H, "A").Value - wsPA.Cells(G, "A").Value).Rows.Count
            
               
                If wsPA.Cells(H, "E").Value > AA.Value + (B * F) And F < C + 10 Then


                
                ElseIf wsPA.Cells(H, "E").Value > AA.Value + (B * F) And F > C + 10 Then
                
                wsTA.Range("A1").End(xlDown).Offset(1, 0).Select
                Selection.Value = "ACTIVE"
                
                wsPA.Cells(H, "A").Value.Copy
                wsTA.Range("A1").End(xlDown).Offset(0, 1).Select
                Selection.Paste PasteSpecial:=xlValues
                
                AA.Value.Copy
                wsTA.Range("A1").End(xlDown).Offset(0, 2).Select
                Selection.Paste PasteSpecial:=xlValues
                
                wsPA.Cells(D, "A").Value.Copy
                wsTA.Range("A1").End(xlDown).Offset(0, 3).Select
                Selection.Paste PasteSpecial:=xlValues
                
                wsPA.Cells(D, "E").Value.Copy
                wsTA.Range("A1").End(xlDown).Offset(0, 4).Select
                Selection.Paste PasteSpecial:=xlValues
                
                wsTA.Range("A1").End(xlDown).Offset(0, 5).Value = C
                
                wsTA.Range("A1").End(xlDown).Offset(0, 6).Value = I
                
                wsTA.Range("A1").End(xlDown).Offset(0, 7).Value = B
                
                wsTA.Range("A1").End(xlDown).Offset(0, 8).Select
                Selection.Paste PasteSpecial:=xlValues
                wsPA.Cells(H, "A").Value.Copy
                wsTA.Range("A1").End(xlDown).Offset(0, 9).Select
                Selection.Paste PasteSpecial:=xlValues
                
                ElseIf wsPA.Cells(H, "E").Value < AA.Value + (B * F) Then
                
                End If
                
                Next R2
                
        Next R1
        
        E = E - 1
        
  Loop
    
       
        Else
            A = A - 1
        End If
    
  Loop
        
        
  Next wsPA




End Sub

 

[SBF12345]

Good question, so I didn't have to scroll up and down the module when I was writing. I was unsure of how many variables I was going to need so I just defined a bunch as integers. I'll need to clean the script after the core elements are in place. Possibly a practice to drop.

 

[Norie]

If you do need to declare multiple variables each variable doesn't need to be on it's own row.

For example.

Dim wsPA As Worksheet, wsTA As Worksheet
Dim PA As Workbook, TA As Workbook
Dim A As Long, B As Long, C As Long ' etc
Dim AA As Range, BB As Range, CC As Range ' etc

Note I've kept variables of the same data type together.

 

[SBF12345]

I'll keep this structure in mind, Thanks,

SBF12345