Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop in Sub Detailloop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop in Sub Detailloop
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 -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Loop in Sub Detailloop
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 -----Original Message----- 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 -- Jim Rech Excel MVP . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop | Excel Discussion (Misc queries) | |||
Loop | Excel Discussion (Misc queries) | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
DO LOOP in VBA | Excel Discussion (Misc queries) | |||
Loop help | Excel Discussion (Misc queries) |