VBA - troubleshooting with .Find method

Question

I am using a .Find method to find cells in a row that contain part of the value "TL" and "CT". What the code currently does is look in every row in column C, trims any variation of "TL-" (ie "TL- ", "TL - ", "TL -") and then limits the numbers after it to only have 6 numbers. Example: if it has 5 numbers, it will add a 0 after the "TL-", if it has 4 numbers, it will add 2 0s after the "TL-" etc.

I have other values in the cells so what it does now is change all the values to do the above method (as shown below)

Start:         Output:
TL-000872  ->  TL-000872
TL-0786    ->  TL-000786
CT-74      ->  TL-000074
GS8; 278K  ->  TL-008278

What I need it to do is the code that is working but only on cells that contain some "TL" value, do the same code but with only 4 numbers on cells that contain some "CT" value, and skip over (leave as is) anything else.

Start:         Output:
TL-000872  ->  TL-000872
TL-0786    ->  TL-000786
CT-74      ->  CT-0074
GS8; 278K  ->  GS8; 278K

My .Find method is definitely not working. I believe that is the main problem; It is not correctly finding the cells with "TL" and "CT". Any suggestions?

NOTE: StartSht is the workbook with the code where all of the values being changed exist.

Dim str As String, ret As String, tmp As String, j As Integer, k As Integer

If Not StartSht.Range("C2").End(xlDown).Find(What:="TL", LookAt:=xlPart, LookIn:=xlValues) Is Nothing Then
For k = 2 To StartSht.Range("C2").End(xlDown).Row
    ret = ""
    str = StartSht.Range("C" & k).Value
        For j = 1 To Len(str)
            tmp = Mid(str, j, 1)
            If IsNumeric(tmp) Then ret = ret + tmp
        Next j

        For j = Len(ret) + 1 To 6
            ret = "0" & ret
        Next
        ret = "TL-" & ret
        StartSht.Range("C" & k).Value = ret

        Next k

ElseIf Not StartSht.Range("C2").End(xlDown).Find(What:="CT", LookAt:=xlPart, LookIn:=xlValues) Is Nothing Then
For k = 2 To StartSht.Range("C2").End(xlDown).Row
    ret = ""
    str = StartSht.Range("C" & k).Value
        For j = 1 To Len(str)
            tmp = Mid(str, j, 1)
            If IsNumeric(tmp) Then ret = ret + tmp
        Next j

        For j = Len(ret) + 1 To 4
            ret = "0" & ret
        Next
        ret = "CT-" & ret
        StartSht.Range("C" & k).Value = ret

Next k

Else

End If

---

EDIT:

The code currently takes a TL that has LESS than 6 numbers following the "TL-" and adds a 0 immediately following "TL-" until the length is six. (ie TL-0098 -> TL-000098, two 0s are added). I need it to also catch if TL has MORE than 6 numbers and delete the zeros immediately following "TL-" until the length is six Easier code might be to simply delete any number following the "-" until the length is six.

EXAMPLE:

TL-0009999   ->  delete one 0  -> TL-009999
TL-0948398   ->  delete one 0  -> TL-948398
TL-00000008  ->  delete two 0s -> TL-000008

Answer 1

Replace your above code with the below one.

Updated Code:

Dim str As String, ret As String, tmp As String, j As Integer, k As Integer

For k = 2 To Sheets("Test").Range("C2").End(xlDown).Row
        ret = ""
        str = Sheets("Test").Range("C" & k).Value

        If InStr(str, "TL") > 0 Then
            For j = 1 To Len(str)
                tmp = Mid(str, j, 1)
                If IsNumeric(tmp) Then
                    ret = ret + tmp
                ElseIf j > 5 And tmp = "T" Then
                    Exit For
                End If

            Next j

            For j = Len(ret) + 1 To 6
                ret = "0" & ret
            Next j

            If Len(ret) > 6 Then
                Debug.Print Len(ret)
                For j = Len(ret) To 7 Step -1
                If Mid(ret, 1, 1) = "0" Then
                    ret = Right(ret, j - 1)
                End If
                Next j
            End If

            ret = "TL-" & ret
            Sheets("Test").Range("C" & k).Value = ret
        ElseIf InStr(str, "CT") Then
            For j = 1 To Len(str)
                tmp = Mid(str, j, 1)
                If IsNumeric(tmp) Then ret = ret + tmp
            Next j

            For j = Len(ret) + 1 To 4
                ret = "0" & ret
            Next
            ret = "CT-" & ret
            Sheets("Test").Range("C" & k).Value = ret
        End If

      Next k