Problem with a Do Loop 2

[RSJohnson]

I am trying to loop through a worksheet and calculate four (4) percentages. At the end of each loop I need to move to the next row and do the same calculations for the new row and so on. I assume that if the myRange is assigned a new value (for the next range) no changes to the functions that do the calculation need to be made because their references to other cells are relative to the new value of myRange or am I wrong, again?

Though the right cell is selected an Error 424 (Object required) is raised at the statement; “Set myRange = myRange.Offset(1, 0).Select”

The variable myRange is dimensioned in the General Declearations.

Can someone suggest what I am doing wrong?
Here’s my code:

Sub CalculatePercents()
Dim myStop As Integer, myCount As Integer

'Determines the number of records (rows) and assigns _
value to variable.
myStop = Range("A1&quot.CurrentRegion.Rows.Count

'Range("CB4&quot.Select
Set myRange = Range("CB4&quot

'Counter needed to end looping.
myCount = 1

'Do Until myStop = myCount
'Calculates funding percentages for a row.
Call GeneralFundPercent
Call SpecialFundPercent
Call TotalFedFunds
Call FederalFundPercent
Call TotalPercent

'Moves next row.
Set myRange = myRange.Offset(1, 0).Select

'Counter
myCount = myCount + 1
'Loop

End Sub

 

[LNBruno]

Just a quick glance, but shouldn't

   'Do Until myStop = myCount

be

   'Do Until myCount = myStop

so that the loop will process more than 1 (the value of myCount) records?

< M!ke >

 

[SkipVought]

Hi,

Try to avoid selecting faq707-4105 How Can I Make My Code Run Faster?

Sub CalculatePercents()
    Dim myStop As Integer, myCount As Integer
    
    'Determines the number of records (rows) and assigns _
        value to variable.
    With Range("A1").CurrentRegion
        myStop = .Rows.Count
        Set rngLoop = Range(Cells(.Row, "CB"), Cells(myStop, "CB"))
    End With
    
    
    For Each myRange In rngLoop
        'Calculates funding percentages for a row.
        Call GeneralFundPercent
        Call SpecialFundPercent
        Call TotalFedFunds
        Call FederalFundPercent
        Call TotalPercent
        
    Next
   
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Just drop the select
try this:
Set myRange = myRange.Offset(1, 0)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[RSJohnson]

Skip,
I dimmed rngLoop as a range and the code worked. How would I get your code to start at CB4 and not CB1?

Mike and Geoff I modified my code with your suggestions and and the code works. Thanks.

 

[SkipVought]

Set rngLoop = Range(Cells(4, "CB"), Cells(myStop, "CB"))

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884