Lookup / Loop / Help Requested

[Spy128Bit]

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!

 

[Guest]

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