For Loop in Sub Detailloop

[Bruce Roberson]

I hate to have to copy and paste all this code in here to
illustrate what I'm trying to do but I didn't know how
else to do it.

Ok, scan on down and notice the "Crows = Cells(1, 1).End
(xlDown).Row" that appears in sub detailloop. I couldn't
figure out why the variable "i" below that didn't get to
the end and stop the for loop, until I put a watch on the
value of "i" and Crows along with some other breakpoints.

The structure of this loop is that it is necessary for it
to call sub summonth to do some summarizing work when it
encounters a "new_prmo". When summonth does its thing, it
has to go back to a header area called headerrecord2 as
long as the condition of prmo is not met(per the else
statement at the bottom of summonth). Header2 does its
thing and then calls detailloop to start process rows in
data again. This is obviously where the value of "i" is
reverting back to a 1, and thus there is no way that "i"
could ever reach the value of crows-1 that is called for
in detailloop.

I had thought before I discovered this condition that I
had to use the following statement at the bottom of
summonth to get out of this thing:
"If Range("Row").Value + 1 > Range("DataRows").Value Then"

But it may be that if I solve the reverting of the For
value "i",back to a value of 1 each time detailloop is
called again, then I may be able to get this thing to wrap
up. Its just almost there, but I can't figure out this
last little piece of getting the flow where I want it so I
can get out of the For loop properly. I've got almost a
week on this project at this point.

The only other thing I could see maybe is if Crows needs a
different calculation to only calculate a value inside of
based on the PRMO column that is being processed at the
time. In that instance, be aware in this particular month,
there are 146 values where prmo = 200306. Then there are 3
right below that with a value of 200305, and then 4 more
below that with a value of 200304. So, there are 154 rows
altogether in the range data counting the field names.
Right nows the Crows formula evaluates to 154. And these
numbers are not finite. I just told you the numbers so you
would know what they are this particular month.

So, I'm open to suggestions as to structure other than
quitting <LOL>. I know once I get it working and stopping
correctly that I have to go back in and clean up all
the .select stuff. I'll do that, but right now I need to
just concentrate on finishing the process.


Sub Process_data()
Range("row, rowsum").Value = 1
Call HeaderRecord1
End Sub
Sub HeaderRecord1()
Sheets("Data_Assembly").Select
Range("Workarea").Select
ActiveCell.Value = Range("Start_of_Report").Offset(0,
0)
ActiveCell.Offset(1, 0).Value = Range
("Start_of_Report").Offset(1, 0)
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Sheets("Report").Select
Range("A1").PasteSpecial xlPasteValues
Sheets("Data_Assembly").Select
Range("WorkArea").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Clear
Call headerrecord2
End Sub
Sub headerrecord2()
Sheets("Data_Assembly").Select
ActiveCell.Offset(0, 0).Value = Range("Start_Month").Offset
(0, 0)
ActiveCell.Offset(1, 0).Value = Range("Start_Month").Offset
(1, 0)
ActiveCell.Offset(2, 0).Value = Range("Start_Month").Offset
(2, 0)
ActiveCell.Offset(3, 0).Value = Range("Start_Month").Offset
(3, 0)
ActiveCell.Offset(4, 0).Value = Range("Start_Month").Offset
(4, 0)
ActiveCell.Offset(5, 0).Value = Range("Start_Month").Offset
(5, 0)
ActiveCell.Offset(6, 0).Value = Range("Start_Month").Offset
(6, 0)
ActiveCell.Offset(7, 0).Value = Range("Start_Month").Offset
(7, 0)
ActiveCell.Offset(8, 0).Value = Range("Start_Month").Offset
(8, 0)
ActiveCell.Offset(9, 0).Value = Range("Start_Month").Offset
(9, 0)
If Range("Fee_Volume").Value = 0 Then
GoTo copyrecords
Else
ActiveCell.Offset(10, 0).Value = Range
("Start_Month").Offset(10, 0)
ActiveCell.Offset(11, 0).Value = Range
("Start_Month").Offset(11, 0)
ActiveCell.Offset(12, 0).Value = Range
("Start_Month").Offset(12, 0)
ActiveCell.Offset(13, 0).Value = Range
("Start_Month").Offset(13, 0)
ActiveCell.Offset(14, 0).Value = Range
("Start_Month").Offset(14, 0)
ActiveCell.Offset(15, 0).Value = Range
("Start_Month").Offset(15, 0)
ActiveCell.Offset(16, 0).Value = Range
("Start_Month").Offset(16, 0)
ActiveCell.Offset(17, 0).Value = Range
("Start_Month").Offset(17, 0)
ActiveCell.Offset(18, 0).Value = Range
("Start_Month").Offset(18, 0)
ActiveCell.Offset(19, 0).Value = Range
("Start_Month").Offset(19, 0)
ActiveCell.Offset(20, 0).Value = Range
("Start_Month").Offset(20, 0)
ActiveCell.Offset(21, 0).Value = Range
("Start_Month").Offset(21, 0)
ActiveCell.Offset(22, 0).Value = Range
("Start_Month").Offset(22, 0)
ActiveCell.Offset(23, 0).Value = Range
("Start_Month").Offset(23, 0)
End If
copyrecords:
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Sheets("Report").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteValues
Sheets("data_assembly").Select
Range("workarea").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Clear
Call detailloop
End Sub

Sub detailloop()
Dim Crows As Long
Sheets("Import").Select
Crows = Cells(1, 1).End(xlDown).Row
For i = 1 To Crows - 1
Sheets("Data_Assembly").Select
Range("Workarea").Select
ActiveCell.Value = Range("Detailloop").Offset(0, 0)
ActiveCell.Offset(1, 0).Value = Range("Detailloop").Offset
(1, 0)
ActiveCell.Offset(2, 0).Value = Range("Detailloop").Offset
(2, 0)
ActiveCell.Offset(3, 0).Value = Range("Detailloop").Offset
(3, 0)
ActiveCell.Offset(4, 0).Value = Range("Detailloop").Offset
(4, 0)
ActiveCell.Offset(5, 0).Value = Range("Detailloop").Offset
(5, 0)
ActiveCell.Offset(6, 0).Value = Range("Detailloop").Offset
(6, 0)
ActiveCell.Offset(7, 0).Value = Range("Detailloop").Offset
(7, 0)
ActiveCell.Offset(8, 0).Value = Range("Detailloop").Offset
(8, 0)
ActiveCell.Offset(9, 0).Value = Range("Detailloop").Offset
(9, 0)
ActiveCell.Offset(10, 0).Value = Range("Detailloop").Offset
(10, 0)
ActiveCell.Offset(11, 0).Value = Range("Detailloop").Offset
(11, 0)
ActiveCell.Offset(12, 0).Value = Range("Detailloop").Offset
(12, 0)
ActiveCell.Offset(13, 0).Value = Range("Detailloop").Offset
(13, 0)
If Range("Do_lse_use") Then
ActiveCell.Offset(14, 0).Value = Range
("lease_use").Offset(0, 0)
ActiveCell.Offset(15, 0).Value = Range
("lease_use").Offset(1, 0)
ActiveCell.Offset(16, 0).Value = Range
("lease_use").Offset(2, 0)
ActiveCell.Offset(17, 0).Value = Range
("lease_use").Offset(3, 0)
ActiveCell.Offset(18, 0).Value = Range
("lease_use").Offset(4, 0)
ActiveCell.Offset(19, 0).Value = Range
("lease_use").Offset(5, 0)
ActiveCell.Offset(20, 0).Value = Range
("lease_use").Offset(6, 0)
ActiveCell.Offset(21, 0).Value = Range
("lease_use").Offset(7, 0)
ActiveCell.Offset(22, 0).Value = Range
("lease_use").Offset(8, 0)
ActiveCell.Offset(23, 0).Value = Range
("lease_use").Offset(9, 0)
ActiveCell.Offset(24, 0).Value = Range
("lease_use").Offset(10, 0)
ActiveCell.Offset(25, 0).Value = Range
("lease_use").Offset(11, 0)
ActiveCell.Offset(26, 0).Value = Range
("lease_use").Offset(12, 0)
ActiveCell.Offset(27, 0).Value = Range
("lease_use").Offset(13, 0)
Else
GoTo copyrecords
End If
copyrecords:
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Sheets("Report").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteValues
Worksheets("Data_Assembly").Select
Range("Workarea").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Clear
If Range("Row").Value + 1 <= Range("DataRows").Value Then
Range("Row").Value = Range("Row").Value + 1
If Range("New_Prmo") Then
Call summonth
End If
Else
Call summonth
End If
If Range("Row").Value + 1 <= Range("DataRows").Value Then
GoTo continue
Else
Exit For
End If
continue:
Next
End Sub

Sub summonth()
Dim sformula As String
Sheets("Data_Assembly").Select
Range("Workarea").Select
ActiveCell.Offset(0, 0).Value = Range("Sum_it").Offset(0,
0)
ActiveCell.Offset(1, 0).Value = Range("Sum_it").Offset(1,
0)
ActiveCell.Offset(2, 0).Value = Range("Sum_it").Offset(2,
0)
ActiveCell.Offset(3, 0).Value = Range("Sum_it").Offset(3,
0)
ActiveCell.Offset(4, 0).Value = Range("Sum_it").Offset(4,
0)
ActiveCell.Offset(5, 0).Value = Range("Sum_it").Offset(5,
0)
ActiveCell.Offset(6, 0).Value = Range("Sum_it").Offset(6,
0)
ActiveCell.Offset(7, 0).Value = Range("Sum_it").Offset(7,
0)
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Sheets("Report").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteValues
Range("A1").End(xlDown).Offset(1, 0).Select
sformula = "=IF(FIXED(INDEX(Summary,ROWSUM+1,2),0,TRUE)
=INDEX(Summary,ROWSUM+1,1)," & _
"""SE~""&FIXED(LINES1,0,TRUE)&""~0001""," &
_
"""SE~""&FIXED(LINES1-LINES2,0)&""~0001"")"
ActiveCell.Formula = sformula
Selection.Copy
ActiveCell.PasteSpecial xlPasteValues
Worksheets("Data_Assembly").Select
Range("Workarea").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Clear
If Range("Row").Value + 1 > Range("DataRows").Value Then
Exit Sub
Else
Range("rowsum").Value = Range("Rowsum").Value + 1
Call headerrecord2
End If
End Sub

 

[Jim Rech]

Bruce-

Don't let this go to your head, but this code is the 97 pound weakling to
Charles Atlas. I'm sure it does something quite simple if only I could
understand it. Your biggest problem is that you have unintended circular
calls (I think). HeaderRecord2 calls DetailLoop which calls summonth which
calls HeaderRecord2. This is circular and will never resolve except by a
macro error. You must restructure this and I'd bet that that restructuring
would require the elimination of summonth's call to HeaderRecord2. At some
point, in any case, some sub has to just return without calling anything.

I don't know what you're doing with i. Nothing within the loop seems to
vary with different i's so it seems to be doing the same thing time after
time.

Btw, code like:

ActiveCell.Offset(0, 0).Value = Range("Sum_it").Offset(0, 0)
ActiveCell.Offset(1, 0).Value = Range("Sum_it").Offset(1, 0)
ActiveCell.Offset(2, 0).Value = Range("Sum_it").Offset(2, 0)
....
ActiveCell.Offset(7, 0).Value = Range("Sum_it").Offset(7, 0)


can be made a one-liner:

ActiveCell.Resize(7).Value = Range("Sum_it").Resize(7).Value

 

[Bruce Roberson]

Jim:

I ended up doing away with all the subs except for the
beginning one called "Process_data"

I made all the other subs be goto areas, and then I had to
refollow the flow to make sure it finished right. I am in
the process of evaulating the file it creates against the
one in QPW for the month. Then if that worked, I have two
closing lines to put in, then to export it to txt which
should be a piece of cake compared to all this other mess.

I've been in Excel 2000 now for going on two months, and
so far, I still say Excel is not nearly as easy or user
friendly to use as Quattro Pro is. Maybe over time my
ideas will change. But right now, if it weren't for the
guys in this newgroup, I would of already given up trying
to program VBA at all.

Once I clean up all the ugly code that I know to clean up,
I'll post back here my best effort to date to get final
suggestions on making it look prettier. Right now, I'm
going to finish the functionality part.

Thanks for your help.


Bruce