Find cell before last that is greater than 0

Question

I have the following code in VBA to find the last cell inside a range that is greater than 0:

Set myRange = .Range(.Cells(1, 14), .Cells(1, 23))
count = 0 'Counter
For Each cll In myRange
   If cll.Value > 0 Then
      count = count + 1
      NoZeroDir = cll.Address
   End If
Next

It gets the address of the last cell greater than 0 in that range. But, how could I get the address from the cell greater than 0 before this last one?

I was thinking of using an offset but that way I'd get the cell before the last > 0 but this cell could not be > 0.

To illustrate it a bit, as an example I have:

2 3 5 0 1 7 0 8 1 0 1

The address from the last cell > 0 would be (1,11) but I want the cell before that one > 0, that is (1,9), not (1,10) as this is 0.

Answer 1

To find the second last number that is >0

Option Explicit

Public Sub FindSecondLastValueGreaterZero()
    Dim MyRange As Range
    Set MyRange = Range("A1:K1")
    
    Const MAXSKIPS As Long = 1  ' skip 1 number that is >0
    Dim Skips As Long
    
    Dim iCol As Long
    For iCol = MyRange.Columns.Count To 1 Step -1
        If MyRange(1, iCol).Value > 0 And Skips < MAXSKIPS Then
            Skips = Skips + 1
        ElseIf MyRange(1, iCol).Value > 0 Then
            Debug.Print "Found at: " & MyRange(1, iCol).Address
            Exit For
        End If
    Next iCol
End Sub

This will start in K loop backwards until it finds a 0 then keeps doing it until skipped >0 is 1 and print the address I1 as result.

Since this loops backwards from right to left it should find the result (in most cases) faster than your code.