Hi,
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
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
