Loop help

[Guest]

Hello all,

I created (recorded) a macro to sort and subtotal raw data with the following:

Sub SubtotalBalSheet()
'
' SubtotalBalSheet Macro
' Macro recorded 10/15/2004 by A24479
'

'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "G/L"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Branch"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Description"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Current Month"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Prior Month"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, HEADER:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Cells.Select
Cells.EntireColumn.AutoFit
Columns("D").Select
Selection.Style = "Comma"
Columns("E:E").Select
Selection.Style = "Comma"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("C1").Select

This portion seems to work well. The next section is where it goes south:

Do Until counter =6000
counter = counter + 1
Selection.End(xlDown).Select
Selection.Copy
Selection.Offset(1, 0).Select
ActiveSheet.Paste
If counter = 6000 Then Exit Do
Loop
Range("A1").Select
End Sub

Once the loop gets to row 65536 it breaks. I've tried to make the loop stop
before that row, but I haven't been able to figure out how. Any help will be
greatly appreciated.

Chris

 

[Frank Stone]

hi,
What is this loop doing(or trying to do)?
as i see it, the loop starts at C1, goes to the bottom of
some data, copies that data, drops down one row and pastes
the data then end up at row 65536 which is where it
crashes.
and does VB know what counter is and what the value of
counter is prior to the loop?
regards
Frank

-----Original Message-----
Hello all,

I created (recorded) a macro to sort and subtotal raw data with the following:

Sub SubtotalBalSheet()
'
' SubtotalBalSheet Macro
' Macro recorded 10/15/2004 by A24479
'

'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "G/L"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Branch"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Description"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Current Month"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Prior Month"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"),

Order1:=xlAscending, HEADER:=xlGuess, _

 

[Guest]

Hi Frank,

Thanks for the reply. I forgot one line of code. Sorry:

counter = 0
Do Until counter = 6000
counter = counter + 1
Selection.End(xlDown).Select
Selection.Copy
Selection.Offset(1, 0).Select
ActiveSheet.Paste
If counter = 6000 Then Exit Do
Loop

The code subtotals the data based on a G/L# in column A and then copies a
G/L description to the subtotal row. I only used the counter and this format
because it was in Help.

 

[Guest]

I got it to work! I used this:

Do
Selection.End(xlDown).Select
If ActiveCell.Address = "$C$65536" Then Exit Do
Selection.Copy
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Loop

Thanks to everyone on this board!

Chris

 

[Frank Stone]

hi again
well if it does what you want, use it.
regards
Frank