VBA Do loops, moving information between sheets

Brayden23

New Member
Joined
Jul 23, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Sorry this is going to be a bit of a mess....
Pretty new to this. Writing code to take a batch input and write it to a batch output sheet. I am getting the first set of the batch, but unsure how to get my loop to continue after blanks and start a new line on the batch output.

Also have to get the info from "User form" and write them to my batch output.

Listed is my code so far for the "Run batch button" on User form

VBA Code:
 Sub EstBatch()

Dim P As Integer
Dim H As Integer
Dim NS As Integer
Dim NL As Integer
Dim BP As Currency
Dim OH As Single
Dim OC As Currency
Dim TP As Currency
Dim PPBR As Currency
Dim EHP As Single
Dim Msg As String
Dim myRow As Integer

Dim userName As String
Dim numberPeople As Single
Dim numberHours As Single
Dim tourDate As Date
myRow = 2


P = Range("C9").Value
H = Range("C10").Value
PPBR = Range("C22").Value
EHP = Range("C23").Value
BP = P * PPBR
NS = Range("C13").Value
NL = Range("C14").Value

Worksheets(2).Select
Cells(1, 1).Activate

'Processes Block
Do
    Worksheets(2).Select
    If IsEmpty(ActiveCell) Then Exit Do
    ActiveCell.Offset(1, 0).Activate
    userName = ActiveCell.Value
    numberPeople = ActiveCell.Offset(1, 0).Value
    numberHours = ActiveCell.Offset(2, 0).Value
    tourDate = ActiveCell.Offset(0, 1).Value
    Do
        If ActiveCell Then Exit Do
        ActiveCell.Offset(1, 0).Activate
    Loop

'Moves down and does next

Worksheets(3).Select
Cells(myRow, 1).Value = userName
Cells(myRow, 2).Value = tourDate
Cells(myRow, 3).Value = numberPeople
Cells(myRow, 4).Value = numberHours
myRow = myRow + 1
ActiveCell.Activate
Loop

End Sub

1658553402028.png
1658553679615.png
1658553717828.png



This code is to do the single batch run on the user form page, it works properly.

VBA Code:
Sub EstSingle()
Dim P As Integer
Dim H As Integer
Dim NS As Integer
Dim NL As Integer
Dim BP As Currency
Dim OH As Single
Dim OC As Currency
Dim TP As Currency
Dim PPBR As Currency
Dim EHP As Single
Dim Msg As String

P = Range("C9").Value
H = Range("C10").Value
PPBR = Range("C22").Value
EHP = Range("C23").Value
BP = P * PPBR
NS = Range("C13").Value
NL = Range("C14").Value

Range("C13").Select
If P < 20 Then
    MsgBox ("Not enough People for tour")
    Range("C9:C10").Value = 0
    Range("C13:C18").Value = 0
    ElseIf P >= 20 And P <= 25 Then
    NS = 1
    NL = 0
    ElseIf P >= 26 And P <= 50 Then
    NS = 2
    NL = 0
    ElseIf P >= 51 And P <= 60 Then
    NL = 1
    NS = 0
    ElseIf P >= 61 And P <= 85 Then
    NL = 1
    NS = 1
    ElseIf P >= 86 And P <= 120 Then
    NL = 2
    NS = 0
    ElseIf P >= 120 Then
     MsgBox ("Too many People for tour")
    Range("C9:C10").Value = 0
    Range("C13:C18").Value = 0
    End If
    Range("C13").Value = NS
    Range("C14").Value = NL

    

Range("C15").Select
ActiveCell.Value = BP

Range("C16").Select
If H > 5 Then
OH = H - 5
Else
OH = 0
End If
ActiveCell.Value = OH

Range("C17").Select
If OH > 0 Then
OC = BP * OH * EHP
Else
OC = 0
End If
ActiveCell.Value = OC

Range("C18").Select
ActiveCell.Value = OC + BP

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Brayden23

New Member
Joined
Jul 23, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Assignment guidelines.. Im stuck lol, thanks for looking.


1658554052993.png
 

Forum statistics

Threads
1,176,640
Messages
5,904,198
Members
435,076
Latest member
71672618

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
Top