Modifying a Macro to Produce Reports

P

PNeil

New Member
Joined
Sep 25, 2019
Messages
4
Hello everyone,
I am a big fan of this forum and have used it to solve many problems by searching through here (I only recently registered). I recently used a Macro created by hiker95 several years back. I am using this macro to sign in/out patients at my facility instead of a paper sign in/out book. This program utilizes a USB Barcode Scanner for input data. I have around 100 patients who are coming and going many times during the day each. Below is a sample I am using to work out bugs.

[TABLE="width: 768"]
<colgroup><col width="228" style="width: 171pt; mso-width-source: userset; mso-width-alt: 8338;"> <col width="23" style="width: 17pt; mso-width-source: userset; mso-width-alt: 841;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;"> <tbody>[TR]
[TD="width: 228, bgcolor: transparent"]Name[/TD]
[TD="width: 23, bgcolor: gray"] [/TD]
[TD="width: 128, bgcolor: transparent"]Time Out(1)[/TD]
[TD="width: 128, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Time In(1)[/TD]
[TD="width: 129, bgcolor: transparent"]Time Out(2)[/TD]
[TD="width: 130, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Time In(2)[/TD]
[TD="width: 127, bgcolor: transparent"]Time Out(3)[/TD]
[TD="width: 130, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]Time In(3)[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Frank Sinatra[/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent, align: right"]9/25/2019 12:38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]9/25/2019 12:45[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Josephina Johnson-Atkinson[/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent, align: right"]9/25/2019 12:38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]9/25/2019 12:45[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Neil Paul[/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent, align: right"]9/25/2019 12:38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]9/25/2019 12:45[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]George Jetson[/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent, align: right"]9/25/2019 12:38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]9/25/2019 12:45[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: gray"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[/TR]
</tbody>[/TABLE]

What I am looking for here is to see if it is possible to create a report, possibly on another sheet or highlighting cell pairs, to display when each patient is out of the building longer than 4hrs [e.g., the difference between Time Out(1) and Time In(1)]. Also, an additional report with total cumulative time out and/or total # of times out of the building.

I manage a residential substance abuse program and we are aiming to increase our accounting of patients. These changes would be used to collect data to prevent/predict relapse and monitor adherence to the program.

I am not able to code in VBA and am thankful for the code written by hiker95. I was only curious about the potential to use this data to further our treatment goals.

link to past post where this program was found. https://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html

Thank you
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi PNeil,
could you add some examples of what you are trying to achieve? The data in your mockup shows only people who left and came back 7 minutes later, right? Being out longer than 4 hours: that's basically B4: =IF(B4<NOW()-4/24,"OUT >4","OK")
You could e.g. use that in conditional formating, say for E2 the formula: =E2<NOW()-4/24
If you can provide a bit more data, someone could help you setting up some calculation sheet that you could then use to make your reports (yeah, also without macros ;))
Cheers,
Koen
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
643
smozgur
smozgur
S
  • Question Question
Conditional formula inconsistently producing #VALUE error
Replies
1
Views
413
CSmith
CSmith
N
  • Locked
  • Question Question
Exccel - date formula that drives multiple other formulas
Replies
2
Views
401
Fluff
Fluff
B
  • Question Question
VBA to show and change list of names depending on selection
Replies
7
Views
751
mumps
mumps
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Forum statistics

Threads
1,224,816
Messages
6,181,143
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top