Object Required Compile Error as part of a For Each Loop

S

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
Greetings,

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

Code: 
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.

Code: 
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
".Row" just returns a number. It is not an object.
You use the "Set" command when setting objects, like ranges.

You could use:
Code: 
Set R1 = wsPA.Cells(G, "A")
If you wanted to loop through rows in relation to this, I don't see why you want to do this:
Code: 
wsPA.Cells(G, "A").Row
as this would just be the value of "G" (since "G" is the row component of cells).
So just use G in your loop.
 
Upvote 0
Re: Joe4

I was trying to loop with the row variable value but ran into some issues. How can I write the for each loop while maintaining a variable range so that the Row begins at row variable G and ends at row variable I.

something like:

Code: 
For Each Row In Row Range

How can I define a row range using a starting variable and an ending variable, G and I in the case of the first range currently defined as the object range CC?
 
Upvote 0
Row is going to return an integer.

Dim row as integer

row=wsPA.Cells(G,"A").Row
Click to expand...
Good programming practices state that you should never used reserved words (names of functions, properties, objects, etc) as the name of your variables, procedures, or functions. It can lead to ambiguity, errors, and unexpected results.

I was trying to loop with the row variable value but ran into some issues. How can I write the for each loop while maintaining a variable range so that the Row begins at row variable G and ends at row variable I.

something like:
Code: 
[COLOR=#333333]For Each Row In Row Range[/COLOR]
Click to expand...
Try something like:
Code: 
Dim myRow as Long
For myRow = G to I
    ...
Next myRow
You can then reference ranges within that loop like:
Code: 
wsPA.Cells(myRow, "A")
 
Upvote 0
Joe4 said:
Good programming practices state that you should never used reserved words (names of functions, properties, objects, etc) as the name of your variables, procedures, or functions. It can lead to ambiguity, errors, and unexpected results.


Try something like:
Code: 
Dim myRow as Long
For myRow = G to I
    ...
Next myRow
You can then reference ranges within that loop like:
Code: 
wsPA.Cells(myRow, "A")
Click to expand...

Yeah this is correct. It was more a quick example but I should have changed the variable name.
 
Upvote 0
Thanks,

I'm running into some other problems at runtime, but I will try the loops in this structure. Originally I had nested loops with the same ranges, but was limited to keeping one variable with the same range. I need to spend some more time with it and see how it goes. Thanks again,

SBF
 
Upvote 0
So the script below is how it stands now. Everything above the For RowLoop1 = G to I appears to be working well. I have been running it line by line with F8. When I get to the For RowLoop line the code is jumping to the K = K-1 line. I would expect the code to at least enter into the variables being defined immediately below the For line. Any ideas as to why this might be occurring?

Code: 
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 RowLoop1 As Long
Dim AA As Range
Dim DD 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)


wsTA.Activate
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents


wsPA.Activate


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
       
        E = A
    
        K = E - 10
    Do Until K = 2
                      
        I = 2
                For RowLoop1 = G To I
        
                H = wsPA.Cells(E, "A").Row
                B = (wsPA.Cells(K, "E").Value - wsPA.Cells(G, "E").Value) / (wsPA.Cells(K, "A").Value - wsPA.Cells(G, "A").Value)
                C = wsPA.Range(wsPA.Cells(K, "A"), wsPA.Cells(G, "A")).Rows.Count
                I = (wsPA.Cells(K, "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(K, "A").Value.Copy
                wsTA.Range("A1").End(xlDown).Offset(0, 3).Select
                Selection.Paste PasteSpecial:=xlValues
                
                wsPA.Cells(K, "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 RowLoop1
        
        K = K - 1
        
  Loop
    Else
            A = A - 1
        End If
    
  Loop
        
        
  Next wsPA


End Sub
 
Upvote 0
I know you've started a new thread but I'm curious - why have you declared so many variables, e.g. A, B, C, D etc?

T
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
125
Peter_SSs
Peter_SSs
S
  • Solved
VBA - Runtime Error 6 - Overflow
Replies
5
Views
65
ststern45
S
E
  • Solved
Summing Values
Replies
3
Views
196
LazyBug
LazyBug
2
  • Question
Run=time error '1004': Method 'Range of Object'_Global' failed
Replies
4
Views
316
2077delta
2
R
  • Question
Macro not working after adding two new items
Replies
4
Views
81
myall_blues
myall_blues

Forum statistics

Threads
1,214,429
Messages
6,119,433
Members
448,897
Latest member
ksjohnson1970

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top