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 October 27th, 2004, 04:12 AM
Registered User
Join Date: Oct 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to changeable Send a message via Yahoo to changeable
Default How to reduce the processing time for this prob?

Hi all! I have a question on how to automated this process.

i have some data which looks like these:
Time Day Y X Speed
6:12:21 Monday 3.137623456 101.6953814 57
6:12:27 Monday 3.136615051 101.6952216 71
6:12:32 Monday 3.135641022 101.6951024 75
6:12:37 Monday 3.134624022 101.6949792 75
6:12:42 Monday 3.133702706 101.6947838 75
6:12:47 Monday 3.132911451 101.6942716 71
6:12:53 Monday 3.132395216 101.6934099 61
     . . . . .
     . . . . .

and i need to do some filtering on it which is based on the following criteria:

Day Time Segment
Monday 7:00:01 - 7:05:00 3.137263<=Y<=3.138149
Tuesday 7:05:01 - 7:10:00 3.136371<=Y<=3.137263
Wednesda 7:10:01 - 7:15:00 3.135472<=Y<=3.136371
Thursday 7:15:01 - 7:20:00 3.134575<=Y<=3.135472
Friday 7:20:01 - 7:25:00 3.133716<=Y<=3.134575
Saturday ... ... 3.13297<=Y<=3.133716
Sunday . Y<=3.13297AND101.693463<=X
              20:55:01 - 21:00:00 101.692597<=X<=101.693463

i.e. I need to have the average speed and standard deviation of a group of data that fall in OR meet the criteria:
certain "Day" (monday....sunday) and within certain "time" (between 7:00:00 to 7:05:00, 7:05:00 to 7:10:00, up to 20:55:00 to 21:00:00) and satisfy certain criteria "segment " (3.137263<=Y<=3.138149.and so on....until the last segment)

I know this can be done by using the "auto filtering", however it is too tedious as i have to repeatly select those criteria one by one which may results to 7(days) x 169 (duration) x 127 (segment criteria) = 150241 trials!!!!

I have tried pivot table too, but it cannot support the numbers of data that i have (around 30-40k). When i try a small portion of my data using pivot tables, instead of select "range of criteria" i.e. (say time from 7:00:00 to 7:05:00) it can only select "a particular criteria" i.e. (say, 7:01:30 or 7:02:01 and etc).

Besides, there are 3 types of criteria in the "segment" criteria. 1) involve satisfying variable Y only, 2) involve satisfying variable X only, 3) involve satisfying variable X and Y.

The expected VBA may be extremely slow, is there any faster way to do this? If there is no, how can i write that slow VBA?

Please guide me in detail as i just start to know VBA not more than 7 days. Any reply is appreciated, thanks!

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
Saxon - run-time error during processing entity ROCXY XSLT 1 June 29th, 2007 04:39 AM
reduce viewstate size vantoko BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 May 10th, 2007 07:20 AM
Speeding up processing time on ASP file sswingle Classic ASP Databases 2 April 14th, 2006 04:51 AM
Solve Application Run Time Prob kvingupta Beginning VB 6 1 March 22nd, 2006 07:15 PM
Reduce the stock when place order helmekki Excel VBA 0 June 20th, 2004 06:20 AM

All times are GMT -4. The time now is 08:54 AM.

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