More Efficient way to get data from a table?

kiwidancer · September 14th, 2009, 12:47 PM

I need to find a value in a table based on 3 factors... The value in Column A (Salesperson Name), the value in Column B (Group Name), and the value in Row 1 which is my header row (Category)

right now, I'm going through cell by cell and seeing if these values match and if they do i'm using the number and getting out of the loop - the code works but is slow

is there a more efficient way? It is taking quite a long time because the table is large and this process has to be done hundreds of times.

my current code (condensed as it contained a lot of things not pertinent to this subject :

Code:

Private Sub calculateComm()
    Dim i As Integer
    Dim comms, pcts As Worksheet
    Dim sp, group, category As String
    Dim tbl, cel As Range
    Set comms = ActiveWorkbook.Worksheets("Month Invoices")
    Set pcts = ActiveWorkbook.Worksheets("Commission Percents")
    Set tbl = pcts.Cells(1, 1).CurrentRegion
    i = 4
    For i = 4 To comms.Cells(4, 1).CurrentRegion.Rows.Count + 2
        sp = Trim(comms.Range("D" & i).Text)
        group = Trim(comms.Range("E" & i).Text)
        category = Trim(comms.Range("N" & i).Text)
        For Each cel In tbl.Cells
            If (pcts.Cells(cel.Row, 1) = sp And pcts.Cells(cel.Row, 2) = group And pcts.Cells(1, cel.Column) = category) Then
                comms.Range("Y" & i) = cel.Value
                Exit For
            End If
        Next cel
        comms.Range("Z" & i).Value = comms.Range("Y" & i).Value * comms.Range("Q" & i).Value
    Next i
End Sub

SheriV · September 14th, 2009, 01:02 PM

Just a thought...I do alot of analysis work myself and I to am always on the hunt for code to make this faster.....How about using code to perform advanced filters? Once you have extracted what you need based on your criteria, you can then use your code to copy your results to your final destination. It should would work faster than cell by cell.....

kiwidancer · September 14th, 2009, 02:40 PM

thanks for the tip man, goes a lot faster now!!

SheriV · September 15th, 2009, 10:33 AM

Your welcome. Have a great day!!

JP · September 18th, 2009, 09:38 AM

In addition to what was already suggested, try reading your worksheet data into arrays and manipulating them in memory. It'll be much faster than going back and forth between Excel and VBA dozens or hundreds of times.

If you need a tutorial, here's a great starting point:

http://www.dailydoseofexcel.com/arch...nge-using-vba/

--JP

Shasur · September 20th, 2009, 06:56 AM

Hi

If the spreadsheet is well-organized then you can try querying with ADO

This would be faster as you can filter the records easily by multiple criteria in SQL statement without doing anything on Excel

Cheers
Shasur