Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup / Loop / Optimization Help Requested
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Excel Worksheet Functions | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Setting up and Configuration of Excel | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Links and Linking in Excel | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Charts and Charting in Excel | |||
Loop removal or optimization | Excel Programming |