Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 15th, 2007, 04:50 PM
Registered User
Join Date: Jan 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to work through huge amounts of data effective

I have 2 excel sheets. One contains userid's and related fields, the other contains userid's and home-drive storage usage. They need to be combined in one sheet.

The obvious way is to loop through one and search in the other for the matching userid, then supplement either one and move on to the next. This will take ages tho and looks both ugly and inefficient.

Is there someone here who could point me in the right direction to go about it more efficient ?

I would be happy to post relevant code if it helps... but it's not that my code is broke, it just takes ages to loop through 90000 records till the record I need is found.

I'm aware that this should be done in a database, but for some reason managers prefer excel sheets where I work.

I was thinking about importing to an access database, do the combining there and then convert back to excel, but I rather take the opportunity to learn about sorting insane amounts of data :)

Thanks in advance.
Reply With Quote
  #2 (permalink)  
Old January 16th, 2007, 01:10 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts


If you are loping thru the cells to find a match it will take time. On the other hand, try to use the find function.


Set rFnd = Workbook.Sheets(1).Range("A1:A60000").Find(what:=s USerID, Lookat:=xlPart)

This will get you the range if the text is found or will be set to nothing

Hence you can check

IF Not rFnd Is Nothing
   CElls(rFnd.row,2).value = sSomeValue
  ' Text Not Found
End if


Reply With Quote
  #3 (permalink)  
Old January 16th, 2007, 08:12 AM
Registered User
Join Date: Jan 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks for the info. That does the trick for me.

Is there any search pattern associated with the Find function so that I could try and learn a bit about effective searching ?


Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to do effective testing on WCF SendilKumar Windows Communication Foundation 1 July 31st, 2009 04:02 AM
View returns huge data,taking more time gunjan.sh SQL Server 2005 15 April 9th, 2008 05:51 AM
Representing Huge Amount of Data In a Html file me_zeta HTML Code Clinic 7 November 6th, 2006 11:23 PM
How to make high effective use of MSXML obj CharlieHower XSLT 9 March 27th, 2006 02:33 AM
Storing Large Amounts Of Data TSEROOGY Classic ASP Databases 3 October 26th, 2004 02:12 AM

All times are GMT -4. The time now is 04:48 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.