Loop within a loop?

[ryancgarrett]

For loop vba

Is it not possible to run a For loop within another For loop? In my code I want it to run a for loop for 3 to 250, but by the time it goes through one whole loop it has gone from 3 to 7, rather than 3 to 4. What am I doing wrong?

Sub postJournalEntries()
    Dim myRow As Integer
    Dim acctNum As Integer
    Dim otherRow As Integer
    
    With Sheets("Journal")
        For myRow = 3 To 250
            If Cells(myRow, 1).Value <> Empty And Cells(myRow, 4) <> "Posted" Then
                acctNum = BlankRow("Journal", myRow, 2) - 2
                otherRow = myRow

                    For otherRow = otherRow To acctNum
                        If Cells(otherRow, 5).Value > 0 Then
                            Sheets(Cells(otherRow, 2).Value).Cells(BlankRow(Cells(otherRow, 2).Value, 3, 1), 1).Value = Date
                            Sheets(Cells(otherRow, 2).Value).Cells(BlankRow(Cells(otherRow, 2).Value, 3, 1) - 1, 4).Value = Cells(otherRow, 5).Value
                        ElseIf Cells(otherRow, 7).Value > 0 Then
                            Sheets(Cells(otherRow, 2).Value).Cells(BlankRow(Cells(otherRow, 2).Value, 3, 1), 1).Value = Date
                            Sheets(Cells(otherRow, 2).Value).Cells(BlankRow(Cells(otherRow, 2).Value, 3, 1) - 1, 5).Value = Cells(otherRow, 5).Value
                        End If
                            Cells(otherRow, 4).Value = "Posted"
                    Next
            End If
        Next
    End With
End Sub

'Function to find the end of a list
Function BlankRow(sName As String, sRow As Integer, sCol As Integer) As Integer
    Do Until Sheets(sName).Cells(sRow, sCol).Value = Empty
        sRow = sRow + 1
    Loop
    
    BlankRow = sRow
End Function

Note, I have noticed that it adds to the value of myRow as it runs through the BlankRow function.

 

[BarCodeWiz]

Ryan,

You are passing your loop counter (myRow) as a reference, and it gets increased in the BlankRow function

acctNum = BlankRow("Journal", myRow, 2) - 2

Try changing your function from:

Function BlankRow(sName As String, sRow As Integer, sCol As Integer) As Integer

To
Function BlankRow(sName As String, ByVal sRow As Integer, sCol As Integer) As Integer

 

[ryancgarrett]

I'm not sure why you're helping people with VBA at 2 AM but I'm pretty sure you are an angel! Lol thanks so much!

 

[Sektor]

You can fully avoid BlankRow function by using only one line:

...
acctNum = Worksheets("Journal").Cells(myRow, 2).End(xlDown).Row -[B][COLOR="Red"] 1[/COLOR][/B]
...

 

[BarCodeWiz]

Haha I don't think I've ever been called an angel, but it has a nice ring to it. Thanks

Onto my 6th cup of coffee...