Lookup / Loop / Optimization Help Requested

S

Spy128Bit

Current macro being used which works just takes a long time to process
a small amount of data.

Sub Chats_Breakdown_Agent()
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

What I'm looking for are possible ways to optimize or change this
macro to be more efficient. Across row 2 on the "Materials" sheet
from E2 to AF2 are the dates of the month. In this case
2/1/07,2/2/07, etc.

Example look at the data sheet is as follows:
Agent Name ID Date Difference Total
Smith, Joe jsmith 2/1/2007 100 500

I am attempting to make a single pass through the data sheet locating
the proper cell on the materials sheet, doing a calculation, then
moving on to the next line. Instead, it looks to looping the entire
data set for not just each line, but each date, and each person. I
considered using an array to cycle through the dates once and when
found then drop out of the loop and move on but those aren't something
I have a lot of experience with but it's being worked on now. Any and
all suggestions or help are appreicated. I've learned a lot by
searching through a great number of posts in the past years but this
one just has me stumped.

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top