UDF for a non-UDF vba

[mrc44]

Is it possible to make the vba code below a UDF function? Instead of running macro with F8 everytime, I'd like to use it with a UDF function only for specific cells? Thanks!

Sub Intervals()    Dim r As range, C As range
    With Cells(1).CurrentRegion
        With .Offset(1).Resize(.Rows.Count - 1)
            For Each r In .Cells
                Set C = .Find(r.Value, r, , 1, , , 2)
                If (C.Address <> r.Address) * (C.Row > r.Row) Then
                    r.Offset(, 6) = C.Row - r.Row - 1
                Else
                    r.Offset(, 6) = "na"
                End If
            Next
        End With
    End With
End Sub

 

[offthelip]

No, simply because a UDF can only write into a single cell, your sub appears to write into lots of cells.

 

[mrc44]

Thanks, offhelip. so is it possible to make it work on a range of cells instead of the whole data?

 

[offthelip]

yes very easily just change the code as below ( I put in the range A1 to G23 just as an example)

Sub Intervals()
Dim r As Range, C As Range
    With Range("A1:G23")
            For Each r In .Cells
                Set C = .Find(r.Value, r, , 1, , , 2)
                If (C.Address <> r.Address) * (C.Row > r.Row) Then
                    r.Offset(, 6) = C.Row - r.Row - 1
                Else
                    r.Offset(, 6) = "na"
                End If
            Next
        End With
End Sub

 

[mrc44]

Thanks again offthelip!