473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

[Excel VBA] Filter records within the time range

255 100+
Hi guys, I'm actually doing this from Access, which requires to connect to an excel worksheet, then calculate the average number of that day within a specific time.

For testing purpose I put the code into excel, and here is the code I had written and tested so far.

Expand|Select|Wrap|Line Numbers
  1.     'RowLoc is data source row number, SheetRow is the output row number
  2.     Dim RowLoc As Integer, SheetRow As Integer
  3.     Dim TimeStart As String, TimeEnd As String
  4.     Dim StackDate As String
  5.     Dim StackTime As String
  6.  
  7.     'StackStart and StackEnd records the row number
  8.     Dim StackStart As Integer, StackEnd As Integer
  9.     Dim Started As Boolean, Ended As Boolean
  10.     Dim EndDate As String    'EOF
  11.  
  12.     RowLoc = 3
  13.     SheetRow = 2
  14.     TimeStart = Format(TimeValue("06:00:00"), "hh:mm:ss AMPM")
  15.     TimeEnd = Format(TimeValue("11:00:00"), "hh:mm:ss AMPM")
  16.     EndDate = Format(E6Stat.Range("A65536").End(xlUp), "Long Date")
  17.  
  18.     Do
  19.         StackDate = E6Stat.Cells(RowLoc, "A")
  20.  
  21.         If StackDate = "" Then
  22.             Exit Do
  23.         Else
  24.             Do While StackDate = E6Stat.Cells(RowLoc, "A")
  25.  
  26.                 StackTime = Format(E6Stat.Cells(RowLoc, "B"), "hh:mm:ss AMPM")
  27.  
  28.                 If StackTime >= TimeStart And StackTime <= TimeEnd And Not Started Then
  29.                     StackStart = RowLoc
  30.                     Started = True
  31.  
  32.                 ElseIf StackTime > TimeEnd And Started And Not Ended Then
  33.  
  34.                     StackEnd = RowLoc - 1
  35.                     Ended = True
  36.  
  37.                 End If
  38.  
  39.                 RowLoc = RowLoc + 1
  40.  
  41.             Loop
  42.  
  43.         End If
  44.  
  45.         'Add the gather informtion to the sheet before moving on
  46.         E6Avg.Cells(SheetRow, "A") = Format(StackDate, "Long Date")
  47.         E6Avg.Cells(SheetRow, "B") = "6:00 AM - 11:00AM"
  48.         E6Avg.Cells(SheetRow, "C").Formula = "=AVERAGE('E6 - Sick'!M" & StackStart & ":M" & StackEnd & ")"
  49.         E6Avg.Cells(SheetRow, "D") = StackStart
  50.         E6Avg.Cells(SheetRow, "E") = StackEnd
  51.         E6Avg.Cells(SheetRow, "F") = StackTime
  52.         E6Avg.Cells(SheetRow, "G") = TimeStart
  53.         E6Avg.Cells(SheetRow, "H") = TimeEnd
  54.  
  55.         SheetRow = SheetRow + 1
  56.  
  57.         Started = False
  58.         Ended = False
  59.  
  60.     Loop While StackDate <> ""
The output for column D to H is to check the value when the result is made.

Currently within the code, the time ranges do not determine if the time from column B is AM or PM. Anyone who knows how to correct this error? Many thanks.
Jul 26 '10 #1

✓ answered by colintis

I found the answer with the InStr function, and this is what I've done by placing a IF statement to the inner IF that compares the time.

Expand|Select|Wrap|Line Numbers
  1. If InStr(8, StackTime, "AM") > 0 Then
  2.     If StackTime >= TimeStart And StackTime <= TimeEnd And Not Started Then
  3.         StackStart = RowLoc
  4.         Started = True
  5.  
  6.     ElseIf StackTime > TimeEnd And Started And Not Ended Then
  7.  
  8.         StackEnd = RowLoc - 1
  9.         Ended = True
  10.  
  11.     End If
  12. End If
  13.  
If there's any better logic than this one to improve performance, please let me know.

1 4162
colintis
255 100+
I found the answer with the InStr function, and this is what I've done by placing a IF statement to the inner IF that compares the time.

Expand|Select|Wrap|Line Numbers
  1. If InStr(8, StackTime, "AM") > 0 Then
  2.     If StackTime >= TimeStart And StackTime <= TimeEnd And Not Started Then
  3.         StackStart = RowLoc
  4.         Started = True
  5.  
  6.     ElseIf StackTime > TimeEnd And Started And Not Ended Then
  7.  
  8.         StackEnd = RowLoc - 1
  9.         Ended = True
  10.  
  11.     End If
  12. End If
  13.  
If there's any better logic than this one to improve performance, please let me know.
Jul 26 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Jon Spragg | last post by:
Hi, I am running an Excel Application that uses a Control Button to invoke a java application that performs some computation and writes the results to a text file. The VB code behind the...
3
by: Gary | last post by:
Sorry to waste everyone's time with a query which is so simple, but as a new user of Access I have created a client database and one of the fields is "next contact due" as the name implies, this is...
4
by: hadardo | last post by:
Hello all. Let me start by saying that I don't use VBA so often, so I ask for your forgivness if my questions is somewhat silly. I'm trying to find if a user typed the same value in two or more...
2
by: pixie | last post by:
Hi, I have some code that I have been using for more than a year. Excel VBA. Today it stopped working. I have no idea why. Here is the code: Range ("E1").Select If IsEmpty(ActiveCell) Then...
3
by: bennyad | last post by:
hi all, i've created an Excel VBA script that shows UserForm1 when clicking on close (the "X" at the top right) without saving. the form simply prompts the user whether to save the file or not...
1
by: helraizer1 | last post by:
Hi folks, I am making a spreadsheet for a foreign exchange bureau, on the loading of the page it asks the operator to add the day's exchange rates. I have the following code in Excel VBA ...
2
by: prakashsakthivel | last post by:
What I want to know is that while one excel application is closed by VBA code from module, another excel application has to be opened. That means, I have written code to open new excel sheet in the...
76
by: JFKJr | last post by:
Hi, the following Excel VBA code is used to select 5 rows by double clicking the cell in the first column corresponding to that row. The code is working fine in excel. But, I need Access VBA code,...
17
by: Blaine | last post by:
I've created the following dll in vb 2008. ___________________________________________________________ Public Interface IDemo Sub doSomething() End Interface Public Class implementIDemo...
2
by: prashantdixit | last post by:
Hi all, I am new to Excel/VBA and would require your help. I have stuck again somewhere and will be highly obliged if you can help me. I have two worksheet 1. Import File Utility (Sheet A)...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.