Hi there,

I have a spreadsheet that has an entire database of tracking numbers that look something like this:

1-15-07-0185
1-15-07-0186
1-15-07-0187
1-15-07-0187.3
1-15-07-0188

But, we have a dashboard (called "Meter Closure Lookup") that only shows us the tracking numbers that match certain parameters and generates a new one. For instance, if the last one was "1-15-07-0188" then the new one is "1-15-07-0189" ([Zone]-[year]-[month]-[incremental]).

My code does that fine, but I'm trying to use it as a function instead of a macro, so when you put =TRACKINGNUMBER in a cell, the number generates, but since all the previous tracking numbers are on another sheets, the function just comes up as an error.

(The MeterID could be a six-digit number like 512512 or 519103, for example)

Function TRACKINGNUMBER(MeterID As String, TheDate As String)

Dim C As Range
Dim i As Integer
Dim ii As Integer
Dim NumList As String
Dim TrackMax As Integer
Dim TrackArr() As String

MeterID = Sheets("Meter Closure Lookup").Range("C3")
TheDate = Sheets("Meter Closure Lookup").Range("E6")

Dim TrackingPrefix As String
Dim TrackingSuffix As String
TrackingPrefix = Mid(MeterID, 4, 1) & "-" & Format(TheDate, "yy-") & Format(TheDate, "mm-")

For Each C In Sheets("Closure Data").Range(Range("F7"), Cells(Rows.Count, 6).End(xlUp))
    If Left(C.Value, Len(TrackingPrefix)) = TrackingPrefix Then
    TrackingSuffix = Right(C.Value, Len(C.Value) - Len(TrackingPrefix))
        If Len(TrackingSuffix) = 4 Then
            NumList = NumList & "," & TrackingSuffix
        End If
    End If
Next C

TrackArr = Split(NumList, ",")

For i = 1 To UBound(TrackArr)
    For ii = 1 To UBound(TrackArr)
        If TrackArr(ii) >= TrackArr(i) Then
            TrackMax = TrackArr(ii)
        End If
    Next ii
Next i
 
TRACKINGNUMBER = TrackingPrefix & Format(TrackMax, "0000")

End Function