CODEWITHSUNDEEP
excelvbaloopsfor-loop
LNKGRDL
LNKGRDL

Reputation: 23

How to apply Step -1 in a For Next to loop in reverse order?

I'm trying to get this loop to check xCol in reverse order, or from the right column to the left. It works without the Step -1 (going from left to right), but does nothing when I add the Step -1.

Sub Email()
    Dim xSheet As Worksheet
    Dim xRow As Integer
    Dim xCol As Integer
    Dim lastRow As Integer
    Dim lastCol As Integer

    Set xSheet = Excel.Worksheets("Approval Process")

    lastRow = xSheet.UsedRange.Rows.Count
    lastCol = xSheet.UsedRange.Columns.Count

    For xRow = 2 To lastRow
        For xCol = 3 To lastCol Step -1
            If xSheet.Cells(xRow, xCol).Value <> "" Then
                MsgBox xRow & "," & xCol
               Exit For
            Else
                MsgBox "Blank"
            End If
        Next xCol
    Next xRow

End Sub

Cells (C2,H5) have either the word "Yes" or are blank.

The end goal is to have the loop return the row and column number for the last cell with data in each row.

Upvotes: 2

Views: 184

Answers (1)

Salamander Krajza
Salamander Krajza

Reputation: 236

When you have negative STEP in FOR loop you should start with bigger number and make the second argument smaller.

When you put smaller number as first input and bigger as second input there is a problem.

Try reverse values ​​to:

For xCol = lastCol To 3 Step -1

Upvotes: 1

Related Questions