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. -
'RowLoc is data source row number, SheetRow is the output row number
-
Dim RowLoc As Integer, SheetRow As Integer
-
Dim TimeStart As String, TimeEnd As String
-
Dim StackDate As String
-
Dim StackTime As String
-
-
'StackStart and StackEnd records the row number
-
Dim StackStart As Integer, StackEnd As Integer
-
Dim Started As Boolean, Ended As Boolean
-
Dim EndDate As String 'EOF
-
-
RowLoc = 3
-
SheetRow = 2
-
TimeStart = Format(TimeValue("06:00:00"), "hh:mm:ss AMPM")
-
TimeEnd = Format(TimeValue("11:00:00"), "hh:mm:ss AMPM")
-
EndDate = Format(E6Stat.Range("A65536").End(xlUp), "Long Date")
-
-
Do
-
StackDate = E6Stat.Cells(RowLoc, "A")
-
-
If StackDate = "" Then
-
Exit Do
-
Else
-
Do While StackDate = E6Stat.Cells(RowLoc, "A")
-
-
StackTime = Format(E6Stat.Cells(RowLoc, "B"), "hh:mm:ss AMPM")
-
-
If StackTime >= TimeStart And StackTime <= TimeEnd And Not Started Then
-
StackStart = RowLoc
-
Started = True
-
-
ElseIf StackTime > TimeEnd And Started And Not Ended Then
-
-
StackEnd = RowLoc - 1
-
Ended = True
-
-
End If
-
-
RowLoc = RowLoc + 1
-
-
Loop
-
-
End If
-
-
'Add the gather informtion to the sheet before moving on
-
E6Avg.Cells(SheetRow, "A") = Format(StackDate, "Long Date")
-
E6Avg.Cells(SheetRow, "B") = "6:00 AM - 11:00AM"
-
E6Avg.Cells(SheetRow, "C").Formula = "=AVERAGE('E6 - Sick'!M" & StackStart & ":M" & StackEnd & ")"
-
E6Avg.Cells(SheetRow, "D") = StackStart
-
E6Avg.Cells(SheetRow, "E") = StackEnd
-
E6Avg.Cells(SheetRow, "F") = StackTime
-
E6Avg.Cells(SheetRow, "G") = TimeStart
-
E6Avg.Cells(SheetRow, "H") = TimeEnd
-
-
SheetRow = SheetRow + 1
-
-
Started = False
-
Ended = False
-
-
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.
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. - If InStr(8, StackTime, "AM") > 0 Then
-
If StackTime >= TimeStart And StackTime <= TimeEnd And Not Started Then
-
StackStart = RowLoc
-
Started = True
-
-
ElseIf StackTime > TimeEnd And Started And Not Ended Then
-
-
StackEnd = RowLoc - 1
-
Ended = True
-
-
End If
-
End If
-
If there's any better logic than this one to improve performance, please let me know.
1 4162
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. - If InStr(8, StackTime, "AM") > 0 Then
-
If StackTime >= TimeStart And StackTime <= TimeEnd And Not Started Then
-
StackStart = RowLoc
-
Started = True
-
-
ElseIf StackTime > TimeEnd And Started And Not Ended Then
-
-
StackEnd = RowLoc - 1
-
Ended = True
-
-
End If
-
End If
-
If there's any better logic than this one to improve performance, please let me know.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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
...
|
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...
|
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,...
|
by: Blaine |
last post by:
I've created the following dll in vb 2008.
___________________________________________________________
Public Interface IDemo
Sub doSomething()
End Interface
Public Class implementIDemo...
|
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)...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |