Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 14th, 2009, 12:47 PM
Authorized User
 
Join Date: Sep 2009
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Unhappy More Efficient way to get data from a table?

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
 
Old September 14th, 2009, 01:02 PM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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.....

Last edited by SheriV; September 14th, 2009 at 01:05 PM..
 
Old September 14th, 2009, 02:40 PM
Authorized User
 
Join Date: Sep 2009
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for the tip man, goes a lot faster now!!
 
Old September 15th, 2009, 10:33 AM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Your welcome. Have a great day!!
 
Old September 18th, 2009, 09:38 AM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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
 
Old September 20th, 2009, 06:56 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Efficient code for beginners.... stevemcd999 BOOK: Beginning C# 3.0 : An Introduction to Object Oriented Programming ISBN: 978-0-470-26129-3 2 February 27th, 2009 06:56 AM
Splitting Table into more efficient structure MauiSpud Access 7 May 12th, 2006 07:35 AM
Most efficient overriding technique jcsdeveloper C# 4 December 7th, 2005 04:18 PM
Database search - more efficient way? SoC Classic ASP Basics 4 August 15th, 2004 08:56 PM
Paging Again Still Not Efficient alyeng2000 ASP.NET 1.0 and 1.1 Basics 0 March 4th, 2004 08:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.