Object Doesn't support this property or method runtime error

[SBF12345]

Greetings,

I am receiving the Object doesn't support this property or method runtime error on the line below:

Set CC = PA.ws.Range(Cells(G, "A"), Cells(I, "A"))

I would like to establish a range variable using two cell values defined by integer variables and rows. I suspect there is a conflict with a property element in the problem line.

This line exists within the script:

[COLOR=#333333]Sub TRENDSHEET()[/COLOR]
Dim ws As Worksheet
Dim PA As Workbook
Dim TA As Workbook
Dim A As Integer
Dim B 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 ws In PA.Worksheets




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


    Do Until A = 2
      
       
        If ws.Cells(A, "E").Value > Application.Large(ws.Cells((A - 10), "E").Resize(21), 2) Then
       
        
      
        Set AA = ws.Cells(A, "E")
        Set DD = ws.Cells(A, "A")
        G = AA.Row
        H = ws.Cells(A, "A").Row
    
        TA.ws.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 = ws.Range(Cells(2, "A")).Row
                      
       
        Set CC = PA.ws.Range(Cells(G, "A"), Cells(I, "A")
                      
        
        D = E - 10
        
        
        Set R1 = PA.ws.Range(Cells(G, "A")).Row
        
            For Each R1 In CC
            
        
            H = PA.ws.Range(Cells(E, "A")).Row
            
            BB = PA.ws.Range(Cells(G, "A"), Cells(I, "A"))
                    
           
            
                Set R2 = PA.ws.Range(Cells(G, "A")).Row
            
                For Each R2 In BB
        
                B = PA.ws.Range(Cells(D, "E").Value - PA.ws.Range(Cells(G, "E")).Value) / PA.ws.Range(Cells(D, "A").Value - PA.ws.Range(Cells(G, "A")).Value)
    
                C = PA.ws.Range(Cells(D, "A"), Cells(G, "A")).Rows.Count
                
                I = PA.ws.Range(Cells(D, "E").Value - PA.ws.Range(Cells(G, "E")).Value)
        
                F = A - (A - H)
            
                J = PA.ws.Range(Cells(H, "A").Value - Cells(G, "A").Value).Rows.Count
            
        
                If PA.ws.Range.Cells(H, "E").Value > AA.Value + (B * F) And F < C + 10 Then
      
            
                ElseIf PA.ws.Range.Cells(H, "E").Value > AA.Value + (B * F) And F > C + 10 Then
                
                TA.ws.Range("A1").End(xlDown).Offset(1, 0).Select
                Selection.Value = "ACTIVE"
                
                PA.ws.Range.Cells(H, "A").Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 1).Select
                Selection.Paste PasteSpecial:=xlValues
                
                AA.Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 2).Select
                Selection.Paste PasteSpecial:=xlValues
                
                PA.ws.Range.Cells(D, "A").Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 3).Select
                Selection.Paste PasteSpecial:=xlValues
                
                PA.ws.Range.Cells(D, "E").Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 4).Select
                Selection.Paste PasteSpecial:=xlValues
                
                TA.ws.Range("A1").End(xlDown).Offset(0, 5).Value = C
                
                TA.ws.Range("A1").End(xlDown).Offset(0, 6).Value = I
                
                TA.ws.Range("A1").End(xlDown).Offset(0, 7).Value = B
                
                TA.ws.Range("A1").End(xlDown).Offset(0, 8).Select
                Selection.Paste PasteSpecial:=xlValues
                PA.ws.Range.Cells(H, "A").Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 9).Select
                Selection.Paste PasteSpecial:=xlValues
               
                ElseIf PA.ws.Range.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 ws

 [COLOR=#333333]  End Sub[/COLOR]

 

[Norie]

The variable PA refers to the workbook 'COMM_TREND.xls' and the loop variable ws refers to worksheets in that workbook and is an object in its own right so you just need ws not PA.ws.

Also, to make sure both instances of Cells refer to the same worksheet you need a worksheet reference for both.

So, this should work,

Set CC = ws.Range(ws.Cells(G, "A"), ws.Cells(I, "A"))

though this is probably a bit neater.

With ws
    Set CC = .Range(.Cells(G, "A"), .Cells(I, "A"))
End With

 

[SBF12345]

Ok, and PA is understood because the loop references ws within the PA object. I am hitting another snag earlier in the code that I somehow slipped by giving the same error "object doesn't support this property or method".

The line doing it is:

TA.ws.Activate

I should mention that the set of ws names in both workbooks PA and TA are the same.

I realize this is outside the scope of the initial post even though its of a similar nature and within the same script. I could create a new thread if its more appropriate.

 

[Norie]

Which worksheet in the workbook that TA refers to are you trying to activate here?

 TA.ws.Activate

 

[SBF12345]

I am trying to activate a worksheet with the same name as in the workbook PA. So in other words both the PA and the TA workbooks have an identical set of worksheets; the set of worksheets in both workbooks are named identically.

So at any moment in the script where TA.ws is referenced TA.ws.Name should be equal to PA.ws.Name

 

[Norie]

I think you need to expand your variable names.

In the loop for the worksheets in the PA workbook try using wsPA instead of just ws,

For Each wsPA in PA.Worksheets
    ' code
Next wsPA

Now in the code within the loop you can set a variable, wsTA, to refer to the worksheet in TA that has the same name as the worksheet you are currently working with.

Set wsTA = TA.Worksheets(wsPA.Name)

With these 2 variables you can refer to, and distinguish between, the worksheets from the 2 different workbooks in the rest of the code.

 

[SBF12345]

Thanks,

This seems to be working well.

SBF12345