Set Object with string from .Find works but not same string from left

[Tanquen]

Are they both not giving me a string? The left function gives a Type Mismatch.

They both seem to give the same string when I test the same functions but output the result in a string variable.

If CL.Value is "TEST_001" they both come back with "TEST"?

Dim rngFoundST As String

rngFoundST = .Find(Replace(CL.Value, "_001", ""), LookIn:=xlValues, LookAt:=xlWhole)
rngFoundST = Left(CL.Value, Len(CL.Value) - 4)

Sub Get_Short_Descriptions_From_Tables()
    '***Get Short Descriptions From the Tables Sheet
        Dim sh As Worksheet, rng As Range, d As String, fRng As Range, CL As Range, Count As Integer, TagNameColumn As Integer, rngFoundST As String
    
        TagNameColumn = Worksheets("Function").Range("B5").Value
    
        Count = 1
                
        rngFoundST = "TEST123"
        rngFoundST = Left("saefasefaswef", Len("asdasefs") - 4)
    
        Set sh = Worksheets("Function")
 
        With sh
            Worksheets("Function").Range("B11:C999").Font.Bold = False
            Worksheets("Function").Range("B11:C999").Interior.ColorIndex = 0
        
            Set fRng = Worksheets("Function").Range("A10:A999")
        
            For Each CL In fRng
                If Not IsEmpty(CL.Value) Then
                    With Worksheets("Tables").Range("A:A")
                        Set rngFound = .Find(Replace(CL.Value, "_001", ""), LookIn:=xlValues, LookAt:=xlWhole)
                        'Set rngFound = Left(CL.Value, Len(CL.Value) - 4)
                        If Not rngFound Is Nothing Then
                            If rngFound.Offset(, TagNameColumn) = "N/A" Then
                                CL.Offset(, 1).Value = Worksheets("Function").Range("A10").Value & "_00" & Count
                                CL.Offset(, 1).Font.Bold = True
                                CL.Offset(, 1).Interior.Color = RGB(255, 242, 204)
                                Count = Count + 1
                            Else
                                CL.Offset(, 1).Value = Replace(rngFound.Offset(, TagNameColumn), "-", "_")
                                CL.Offset(, 1).Font.Bold = True
                                CL.Offset(, 2).Value = Replace(rngFound.Offset(, 1), "-", "_")
                                CL.Offset(, 2).Font.Bold = True
                                CL.Offset(, 3).Value = Replace(rngFound.Offset(, 2), "_", "-")
                            End If
                        Else
                            If InStr(CL.Value, "_001") Then
                              CL.Offset(, 1).Interior.Color = RGB(255, 255, 155)
                            End If
                        End If
                    End With
                End If
            Next CL
        End With
End Sub

 

[astrodon]

Are they both not giving me a string? The left function gives a Type Mismatch.

They both seem to give the same string when I test the same functions but output the result in a string variable.

If CL.Value is "TEST_001" they both come back with "TEST"?

Dim rngFoundST As String

rngFoundST = .Find(Replace(CL.Value, "_001", ""), LookIn:=xlValues, LookAt:=xlWhole)
rngFoundST = Left(CL.Value, Len(CL.Value) - 4)

Sub Get_Short_Descriptions_From_Tables()
    '***Get Short Descriptions From the Tables Sheet
        Dim sh As Worksheet, rng As Range, d As String, fRng As Range, CL As Range, Count As Integer, TagNameColumn As Integer, rngFoundST As String
   
        TagNameColumn = Worksheets("Function").Range("B5").Value
   
        Count = 1
               
        rngFoundST = "TEST123"
        rngFoundST = Left("saefasefaswef", Len("asdasefs") - 4)
   
        Set sh = Worksheets("Function")
 
        With sh
            Worksheets("Function").Range("B11:C999").Font.Bold = False
            Worksheets("Function").Range("B11:C999").Interior.ColorIndex = 0
       
            Set fRng = Worksheets("Function").Range("A10:A999")
       
            For Each CL In fRng
                If Not IsEmpty(CL.Value) Then
                    With Worksheets("Tables").Range("A:A")
                        Set rngFound = .Find(Replace(CL.Value, "_001", ""), LookIn:=xlValues, LookAt:=xlWhole)
                        'Set rngFound = Left(CL.Value, Len(CL.Value) - 4)
                        If Not rngFound Is Nothing Then
                            If rngFound.Offset(, TagNameColumn) = "N/A" Then
                                CL.Offset(, 1).Value = Worksheets("Function").Range("A10").Value & "_00" & Count
                                CL.Offset(, 1).Font.Bold = True
                                CL.Offset(, 1).Interior.Color = RGB(255, 242, 204)
                                Count = Count + 1
                            Else
                                CL.Offset(, 1).Value = Replace(rngFound.Offset(, TagNameColumn), "-", "_")
                                CL.Offset(, 1).Font.Bold = True
                                CL.Offset(, 2).Value = Replace(rngFound.Offset(, 1), "-", "_")
                                CL.Offset(, 2).Font.Bold = True
                                CL.Offset(, 3).Value = Replace(rngFound.Offset(, 2), "_", "-")
                            End If
                        Else
                            If InStr(CL.Value, "_001") Then
                              CL.Offset(, 1).Interior.Color = RGB(255, 255, 155)
                            End If
                        End If
                    End With
                End If
            Next CL
        End With
End Sub

        Set rngFound = .Find(Replace(CL.Value, "_001", ""), LookIn:=xlValues, LookAt:=xlWhole)

Not real familiar with the Find method but shouldn't Replace precede find?

 

[Tanquen]

I think I messed it up.

I need to replace just the "Replace" function not the whole thing.


It should be like this:

Set rngFound = .Find(Left(CL.Value, Len(CL.Value) - 4), LookIn:=xlValues, LookAt:=xlWhole)

Can I delete the thread?