breaking up time between 2 dates - displaying data for each day

Hi,

I'm trying to calculate information per day, between 2 dates and have it laid out in a spreadsheet I have setup. The information is as follows:

I'm currently working on an excel spreadsheet that calculates emissions (volatile organic compounds, and hazardous air pollutants) for a client. On the "Data Entry" sheet users input in the following:

B4 - Start date (example - 1/1/15)
C4 - Start Time (example - 12:00PM)
D4 - End date (example - 1/2/15)
E4 - End Time (example - 12:00PM)
F4 - Product Name (example - DB305)
G4 - Batch amount - (example - 5000 lbs)
H7 - VOC Average (lookup using vlookup)
H8 - VOC total Batch Emissions (H7 * batch amount)
H9 - HAP Average (lookup using vlookup)
H10 - HAP total batch emissions (H9 * Batch amount)

From the user input data, I use Vlookups to calculate VOC and HAP emissions depending on the product and batch amount entered. I have a command button that takes the user input data and moves one sheet over and pastes the data in the "Batch Entries" worksheet.

"Batch Entries":
Column B = StartDate/Time (DataEntry B4+ DataEntry C4)
Column C = EndDate/Time (DataEntry D4 + DataEntry E4)
Column D = Product
Column E = Batch Amount
Column F = Total Batch Time (EndDate/Time - StartDate/Time)
Column G = VOC Emissions (calculated in data entry, Vlookup value X Batch amount)
Column H = HAP Emissions (calculated in data entry, Vlookup value X Batch amount)

From here I can calculate hourly, daily and monthly emissions.

The issue is, if the user inputs say Jan 1 12:00PM - Jan 3 12:00PM, it will print the following in "Batch Entries"
B4 - 1/1/15 12:00PM
C4 - 1/3/15 12:00PM
D4 - FX504 (Product name)
E4 - Batch Amount 8,317 (user entered batch amount)
F4 - 48.00 (total batch hours)
G4 - 0.533 lbs (calculated VOC amount)
H4 - 0.532 lbs (calculated HAPS amount)

The facility needs to know emissions on a daily timeline, so I need some sort of code to look at the data and break it up if the batch spans over several days:

Jan 1 12:00PM - Jan 1 11:59 PM FX504 8,317 12 hours voc amount HAP amount
Jan 2 12:00 AM - Jan 2 11:59 PM FX504 8,317 24 hours voc amount HAP amount
Jan 3 12:00 AM - Jan 3 12:00 PM FX504 8,317 12 hours voc amount HAP amount

The code to my command button on the user input sheet is as follows:

Private Sub CommandButton1_Click()
Dim StartDateTime As Single
Dim EndDateTime As Single
Dim Date1 As Single
Dim Product As String
Dim Batchlb As Single
Dim BatchTime As Single
Dim VOCTotal As Single
Dim HAPTotal As Single
Dim SumIfDate As Single
    Worksheets("Data Entry").Select
    StartDateTime = Range("B4") + Range("C4")
    EndDateTime = Range("D4") + Range("E4")
    Product = Range("F4")
    Batchlb = Range("H4")
    BatchTime = Range("I4")
    VOCTotal = Range("H8")
    HAPTotal = Range("H10")
    Worksheets("Batch Entries").Select
    Worksheets("Batch Entries").Range("B2").Select
    If Worksheets("Batch Entries").Range("B2").Offset(1, 0) <> "" Then
    Worksheets("Batch Entries").Range("B2").End(xlDown).Select
    End If
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = StartDateTime
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = EndDateTime
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Product
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Batchlb
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = BatchTime
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = VOCTotal
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = HAPTotal
    Worksheets("Data Entry").Select
End Sub

Any help would be greatly appreciated as I am completely stuck!

Zach

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I have added them for you - this time

Sorry about that, thanks so much for adding.

I've also added the excel workbook, probably much easier actually being able to take a look.