Answered step by step
Verified Expert Solution
Question
1 Approved Answer
This VBA code only brings up 1 row of results in the Batch Output sheet. How do I change this code to output all of
This VBA code only brings up row of results in the "Batch Output" sheet. How do I change this code to output all of the rest of the data?
Sub RunBatch
Declarations
Dim P As Integer
Dim H As Double
Dim PPBR As Double
Dim EHP As Double
Dim BP As Double
Dim OH As Double
Dim OC As Double
Dim TP As Double
Dim smallBuses As Integer
Dim largeBuses As Integer
Dim currentRow As Integer
Dim outputRow As Integer
Read parameters
PPBR SheetsUser Form"RangeCValue
EHP SheetsUser Form"RangeCValue
Initialize starting row for output
outputRow
Initialize starting row for input
currentRow
Loop through batch input until the end of data
Do While SheetsBatch Input"CellscurrentRowValue
Read inputs and ensure they are the correct data type
P SheetsBatch Input"CellscurrentRow Value
H SheetsBatch Input"CellscurrentRow Value
Check if P and H are numeric
If Not IsNumericP Or Not IsNumericH Then
MsgBox "Invalid data at block starting at row & currentRow & Please ensure all data is numeric."
Exit Sub
End If
Determine number of buses needed
If P Then
smallBuses
largeBuses
ElseIf P Then
smallBuses
largeBuses
ElseIf P Then
smallBuses
largeBuses
ElseIf P Then
smallBuses
largeBuses
Else
smallBuses
largeBuses
End If
Calculate prices
BP P PPBR
If H Then
OH H
If OH Then OH
OC BP OH EHP
Else
OH
OC
End If
TP BP OC
Output results to Batch Output sheet
SheetsBatch Output"CellsoutputRowValue SheetsBatch Input"CellscurrentRowValue Customer Name
SheetsBatch Output"CellsoutputRowValue SheetsBatch Input"CellscurrentRowValue Date Trip Date
SheetsBatch Output"CellsoutputRowValue P People
SheetsBatch Output"CellsoutputRowValue H Hours
SheetsBatch Output"CellsoutputRowValue PPBR PPBR
SheetsBatch Output"CellsoutputRowValue EHP EHP
SheetsBatch Output"CellsoutputRowValue smallBuses NS Number of Small buses
SheetsBatch Output"CellsoutputRowValue largeBuses NL Number of Large buses
SheetsBatch Output"CellsoutputRowValue BP BP Base Price
SheetsBatch Output"CellsoutputRowValue OH OH Overtime Hours
SheetsBatch Output"CellsoutputRowValue OC OC Overtime Charge
SheetsBatch Output"CellsoutputRowValue TP TP Total Price
Move to the next block
currentRow currentRow
outputRow outputRow
Loop
End Sub
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started