Run function for multiple data sets and output results to different cells

Question

I have been trying forever to try and figure this out. I have a set of data in a certain sheet in my Excel file. I have written code so that it outputs some of that information to another sheet. I don't know how to get the function to loop through all the different data sets and output them into the "Output" sheet in my excel file on different rows.

This is what I have so far. Can someone please help?

How do I get the function to run through about 6 data sets that include 5 cells in the column until there are 2 blank cells?
How do I output those different results to another sheet? I already have them outputting the first data set and it works fine. I just need to know how to do the other ones.

Thank you!

Sub EstBatch()

'variables
Dim N As String
Dim D As Date
Dim P As Integer
Dim H As Single
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 batches As Range

'inputs
N = Sheets("Batch Input").Range("A1").Value
D = Sheets("Batch Input").Range("B1").Value
P = Sheets("Batch Input").Range("A2").Value
H = Sheets("Batch Input").Range("A3").Value
PPBR = Sheets("User Form").Range("C22").Value
EHP = Sheets("User Form").Range("C23").Value

Range("A1").Select
'Processes

BP = P * PPBR
OH = H - 5

If P > 120 Or P < 20 Then
    MsgBox ("Cannot Accommodate Group")
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
    NS = 0
    NL = 1
ElseIf P >= 61 And P <= 85 Then
    NS = 1
    NL = 1
ElseIf P >= 86 And P <= 120 Then
    NS = 0
    NL = 2
End If

If OH > 4 Then
OH = 4
OC = BP * OH * EHP
ElseIf 0 < OH <= 4 Then
OC = BP * OH * EHP
ElseIf OH <= 0 Then
OC = 0
End If

TP = BP + OC


'outputs
Sheets("Batch Output").Range("A2").Value = N
Sheets("Batch Output").Range("B2").Value = D
Sheets("Batch Output").Range("C2").Value = P
Sheets("Batch Output").Range("D2").Value = H
Sheets("Batch Output").Range("E2").Value = PPBR
Sheets("Batch Output").Range("F2").Value = EHP
Sheets("Batch Output").Range("G2").Value = NS
Sheets("Batch Output").Range("H2").Value = NL
Sheets("Batch Output").Range("I2").Value = BP
Sheets("Batch Output").Range("J2").Value = OH
Sheets("Batch Output").Range("K2").Value = OC
Sheets("Batch Output").Range("L2").Value = TP
End Sub

score 1 · Answer 1 · edited May 23 '17 at 12:20

Welcome to StackOverflow. Great first question.

I think what you're reaching for is how to use loops in solving a problem like this.

One easy way to do loops is with a counter, as in the examples I've given below. If appropriate, you can also use a range of cells to loop through data, as described in this answer: https://stackoverflow.com/a/19394207/2665195.

Starting with your second question: if you want a separate sheet for each output you can use Sheets.Add and paste into that new sheet. To do this you will want to use a variable naming convention like Sheets("Batch Output" & X).Range. In this way you can Dim X as Integer and loop through the process incrementing the X integer with each loop. Here's some sample code you can adapt for your purpose:

Sub ExampleAddSheets()

    Dim intX As Integer
        intX = 1
    Dim wsBatchOutput As Worksheet

    For intX = 1 To 6
        Set wsBatchOutput = Worksheets.Add 'adds a worksheet and tags it to a variable
         wsBatchOutput.Name = "BatchOutput" & intX 'names the worksheet
        wsBatchOutput.Range("A1").Value = "Data here.  Example " & intX 
    Next intX

    Set wsBatchOutput = Nothing

End Sub

I don't know what your data source looks like, but hopefully it is set up in a way that you can turn the inputs aquisition into a loop. For example, if the data came into the system in rows (which your example does not seem to do) you could just increment the row number, something like this:

Sub ExampleSetInputs()

    'variables
        Dim N As String
        Dim D As Date
        Dim P As Integer
        Dim H As Single
        Dim PPBR As Currency
        Dim EHP As Single
        Dim intRow As Integer
            intRow = 2

    'inputs
        For intRow = 2 To 7
            N = Sheets("Batch Input").Range("A" & intRow).Value
            D = Sheets("Batch Input").Range("B" & intRow).Value
            P = Sheets("Batch Input").Range("C" & intRow).Value
            H = Sheets("Batch Input").Range("D" & intRow).Value
        Next intRow

End Sub

I hope this helps with your challenge.

Run function for multiple data sets and output results to different cells

1 Answers1