Answered step by step
Verified Expert Solution
Link Copied!

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 1 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 = Sheets("User Form").Range("C22").Value
EHP = Sheets("User Form").Range("C23").Value
' Initialize starting row for output
outputRow =2
' Initialize starting row for input
currentRow =1
' Loop through batch input until the end of data
Do While Sheets("Batch Input").Cells(currentRow,1).Value >""
' Read inputs and ensure they are the correct data type
P = Sheets("Batch Input").Cells(currentRow +1,1).Value
H = Sheets("Batch Input").Cells(currentRow +2,1).Value
' Check if P and H are numeric
If Not IsNumeric(P) Or Not IsNumeric(H) 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 =25 Then
smallBuses =1
largeBuses =0
ElseIf P =50 Then
smallBuses =2
largeBuses =0
ElseIf P =60 Then
smallBuses =0
largeBuses =1
ElseIf P =85 Then
smallBuses =1
largeBuses =1
Else
smallBuses =0
largeBuses =2
End If
' Calculate prices
BP = P * PPBR
If H >5 Then
OH = H -5
If OH >4 Then OH =4
OC = BP * OH * EHP
Else
OH =0
OC =0
End If
TP = BP + OC
' Output results to Batch Output sheet
Sheets("Batch Output").Cells(outputRow,1).Value = Sheets("Batch Input").Cells(currentRow,1).Value ' Customer (Name)
Sheets("Batch Output").Cells(outputRow,2).Value = Sheets("Batch Input").Cells(currentRow,2).Value ' Date (Trip Date)
Sheets("Batch Output").Cells(outputRow,3).Value = P ' People
Sheets("Batch Output").Cells(outputRow,4).Value = H ' Hours
Sheets("Batch Output").Cells(outputRow,5).Value = PPBR ' PPBR
Sheets("Batch Output").Cells(outputRow,6).Value = EHP ' EHP
Sheets("Batch Output").Cells(outputRow,7).Value = smallBuses ' NS (Number of Small buses)
Sheets("Batch Output").Cells(outputRow,8).Value = largeBuses ' NL (Number of Large buses)
Sheets("Batch Output").Cells(outputRow,9).Value = BP ' BP (Base Price)
Sheets("Batch Output").Cells(outputRow,10).Value = OH ' OH (Overtime Hours)
Sheets("Batch Output").Cells(outputRow,11).Value = OC ' OC (Overtime Charge)
Sheets("Batch Output").Cells(outputRow,12).Value = TP ' TP (Total Price)
' Move to the next block
currentRow = currentRow +3
outputRow = outputRow +1
Loop
End Sub
image text in transcribed

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Databases questions