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
Register
Register | 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
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 15th, 2007, 03: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, 12:10 AM
Friend of Wrox
Points: 2,950, Level: 22
Points: 2,950, Level: 22 Points: 2,950, Level: 22 Points: 2,950, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 788
Thanks: 1
Thanked 51 Times in 47 Posts
Default

Planet

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.

like

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
Else
  ' Text Not Found
End if

Cheers:)
Shasur

http://www.vbadud.blogspot.com
Reply With Quote
  #3 (permalink)  
Old January 16th, 2007, 07:12 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 ?

Regards,

Jeroen
Reply With Quote
Reply


Thread Tools
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 10:23 PM
How to make high effective use of MSXML obj CharlieHower XSLT 9 March 27th, 2006 01: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 08:56 PM.


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