VBA to locate first cell in range with value > 0

[EssKayKay]

Hello,
I’m not quite sure about this so please bear with me – sorry so being vague.
I’m looking for a VBA routine that will locate/return the cell location of the first (or last) cell in a range that contains a value greater than zero.

Example –
Range “U33:U200” contains formulas that displays either a value or zero. I want to know where the first/last cell(s) are that contain values > 0.

Thanks for viewing,
Steve K.

 

[EssKayKay]

I should have noted, the values are all consecutive with no values before or after the cells that are > 0. As example - there could be values greater than zero in U44:U55 but all other cells before U44 and after U55 will contain a zero.

 

[Phuoc]

Like this:

Public Sub Test()
Dim cel As Range, first_cell As String, last_cell As String
For Each cel In Range("U33:U200")
If cel.Value > 0 Then
    If first_cell = "" Then
        first_cell = cel.Address(0, 0)
    Else
        last_cell = cel.Address(0, 0)
    End If
End If
Next
MsgBox "first cell: " & first_cell & ", last cell: " & last_cell
End Sub

 

[EssKayKay]

Like this:

Public Sub Test()
Dim cel As Range, first_cell As String, last_cell As String
For Each cel In Range("U33:U200")
If cel.Value > 0 Then
    If first_cell = "" Then
        first_cell = cel.Address(0, 0)
    Else
        last_cell = cel.Address(0, 0)
    End If
End If
Next
MsgBox "first cell: " & first_cell & ", last cell: " & last_cell
End Sub

Thank you very much Phuoc for your quick response. This is doing exactly what I was hoping for. I have some more things to dress up a bit but this most certainly is pointing my in the right direction and looks very promising.

Again, much appreciated. . .
SKK

 

[Fuji]

This will ignore negative value and string etc, so true value that are greater than 0.

Sub test()
    Dim x&, y&
    x = [min(if(isnumber(U33:U200),if(U33:U200>0,row(U33:U200))))]
    If x = 0 Then MsgBox "No value greater than 0": Exit Sub
    y = [max(if(isnumber(U33:U200),if(U33:U200>0,row(U33:U200))))]
    MsgBox "First cell is in row " & x & vbLf & "Last Cel is in row " & y
End Sub

 

[EssKayKay]

Excellent - Thank you Fuji.
Both of these do exactly what I was looking for.

Again, much appreciated both Phuoc & Fuji - you guys are amazing. . .
Steve K.

 

[EssKayKay]

I have one more question Fuji if you please. Your code does exactly what I wanted as I noted in my example. However, the range periodically changes. It always starts in cell U33 but the last cell may change. The last row number is stored in cell T13. So, in my example, T13=200. The way I have been handling this is with a code line similar to the following:
Range("U33:U" & Range("T13").Value)
However, I have not been able to apply this to your code. Obviously I’m missing something.

 

[Fuji]

When you need to use variable, use Evaluate method like

Sub test()
    Dim x&, y&, s$
    s = "U33:U" & Range("t13").Value
    x = Evaluate(Replace("min(if(isnumber(#),if(#>0,row(#))))", "#", s))
    If x = 0 Then MsgBox "No value greater than 0": Exit Sub
    y = Evaluate(Replace("max(if(isnumber(#),if(#>0,row(#))))", "#", s))
    MsgBox "First cell is in row " & x & vbLf & "Last Cel is in row " & y
End Sub

 

[EssKayKay]

Ahhh - thank you very much Fuji. That does exactly what I was looking for. I did try adding another variable for the the range but was not aware of the "Evaluate" method. Learned something new today.

Again, much appreciated,
Steve