Iterating through a range until you find different value in VBA

Question

I'm trying to create a VBA function that starts from the bottom of a range, and returns the first value that's different from the value at the bottom.

Example:

In the above table, I'd like to be able to grab the last value in the "Month" column (11), and iterate to the top until the value 10 is reached, and return that value.

I just started looking into VBA 3 days ago and am very unfamiliar with the language so I'm still trying to grok the syntax.

I have no doubt that my thinking is fuzzy with this, so I'd really appreciate feedback on my errors.

Here's what I have right now:

Code:

Function NextValue(num1 As Range)
For c = num1.End(xlDown) To num1.Item(1)
    If Cells(c, 1) <> num1.End(xlDown) Then
        NextValue = Cells(c, 1)
        Exit For
    End If
Next c
End Function

In case it's not clear, here's a description of what I'm trying to do, line-by-line.

1). Initiate a For-Loop that begins at the end of a range and decrements to the top

2). Check if that cell does not match the last value in that column

3). If it does not, then set the value of the function to that value

4). Terminate If statements, For loops, and end the function.

Your help is greatly appreciated.

Answer 1

Try this:

Function NextValue(num1 As Range) as Integer
  Dim y As Integer
    'get the last cell from num1
    Set num1 = num1.End(xlDown)

    y = -1
    Do Until num1.Offset(y, 0).Value <> num1.Value
        y = y - 1
    Loop
    'set function return to the different cell
    NextValue = num1.Offset(y, 0).value
End Function

Answer 2

This will handle both compact ranges and disjoint ranges:

Option Explicit

Public Function SomethingElse(rng As Range) As Variant
    Dim r As Range, values() As Variant
    Dim i As Long, strvalue As Variant
    ReDim values(1 To rng.Count)

    i = 1
    For Each r In rng
        values(i) = r.Value
        i = i + 1
    Next r

    strvalue = values(rng.Count)

    For i = rng.Count To 1 Step -1
        If values(i) <> strvalue Then
            SomethingElse = values(i)
            Exit Function
        End If
    Next i

    SomethingElse = CVErr(xlErrNA)
End Function