Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup / Loop / Help Requested
Works as seen below:
Sub Breakdown() Dim i As Long Dim a As Integer Application.Calculation = xlCalculationManual 'Application.ScreenUpdating = False a = 0 LastDataRow = Sheets("Data").UsedRange.Rows.Count LastRow = Sheets("Material").UsedRange.Rows.Count For a = 2 To LastDataRow For i = 3 To LastRow If Sheets("Data").Range("B" & a) = Sheets("Material").Range("D" & i) And Sheets("Data").Range("C" & a) = Sheets("Material").Range("E2") Then Sheets("Material").Range("E" & i) = (Sheets("Data").Range("D" & a) + Sheets("Data").Range("E" & a)) / Sheets("Data").Range("E" & a) GoTo NextData: End If If Sheets("Data").Range("B" & a) = Sheets("Material").Range("D" & i) And Sheets("Data").Range("C" & a) = Sheets("Material").Range("F2") Then Sheets("Material").Range("F" & i) = (Sheets("Data").Range("D" & a) + Sheets("Data").Range("E" & a)) / Sheets("Data").Range("E" & a) GoTo NextData: End If Next NextData: Next a = 0 'Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub I am looking for suggestions into how to optimize my loops so I am not looping through so much data unnecessarily. Range E2:AF2 on the materials sheet are the dates from 2/1/07 to 2/28/07. I limited what is seen to two dates only but would like to loop through them all. The current macro works, just very slowly and when I watch the loops I can't figure out how to keep from looping when it isn't necessary. I am trying to make noe pass over the data and fill in the appropriate spot on the materials sheet. I am considering using an array for the E2:AF2 range but ran into some trouble implementing so far. Any and all help or suggestions are appeciated. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup / Loop / Help Requested
I replaced th GOTO statement with Exit For. don't know if the goto are
slowing the execution. Aviod using GOTO. To get out of a double for loop you can add a boolean MyExit = False for i = 1 to 5 for j = 3 to 8 if a < b then Myexit = True Exit for exit if Next j if Myexit = true then exit for Next i Sub Breakdown() Dim i As Long Dim a As Integer Application.Calculation = xlCalculationManual 'Application.ScreenUpdating = False a = 0 LastDataRow = Sheets("Data").UsedRange.Rows.Count LastRow = Sheets("Material").UsedRange.Rows.Count For a = 2 To LastDataRow For i = 3 To LastRow If Sheets("Data").Range("B" & a) = _ Sheets("Material").Range("D" & i) _ And Sheets("Data").Range("C" & a) = _ Sheets("Material").Range("E2") Then Sheets("Material").Range("E" & i) = (Sheets("Data").Range("D" & a) + _ Sheets("Data").Range("E" & a)) / Sheets("Data").Range("E" & a) Exit For End If If Sheets("Data").Range("B" & a) = _ Sheets("Material").Range("D" & i) _ And Sheets("Data").Range("C" & a) = Sheets("Material").Range("F2") Then Sheets("Material").Range("F" & i) = (Sheets("Data").Range("D" & a) + _ Sheets("Data").Range("E" & a)) / Sheets("Data").Range("E" & a) Exit for End If Next Next a = 0 'Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub I am looking for suggestions into how to optimize my loops so I am not looping through so much data unnecessarily. Range E2:AF2 on the materials sheet are the dates from 2/1/07 to 2/28/07. I limited what is seen to two dates only but would like to loop through them all. The current macro works, just very slowly and when I watch the loops I can't figure out how to keep from looping when it isn't necessary. I am trying to make noe pass over the data and fill in the appropriate spot on the materials sheet. I am considering using an array for the E2:AF2 range but ran into some trouble implementing so far. Any and all help or suggestions are appeciated. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help requested | Excel Worksheet Functions | |||
Lookup / Loop / Optimization Help Requested | Excel Programming | |||
Help Requested | Excel Programming | |||
A challenge, Serious help requested | New Users to Excel | |||
Conditional Lookup and copy loop between worksheets | Excel Programming |