Trying to find a value in a specified column

Question

Previous ques https://www.mrexcel.com/board/threads/run-time-error-13.1163596/page-2#posts

Trying to find a specified value in a column C "WINGS" then go 3 columns across then 1 down and fill that cell with a value. Which is in a case function see below

Private Sub TextBox6_Change() Dim FirstAddress As String Dim MyArr As Variant Dim Rng As Range Dim ws As Worksheet Dim I As Long

Set ws = ThisWorkbook.Worksheets("Job Card Master")

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

'Search for a Value Or Values in a range
'You can also use more values like this Array("Dove", "Sky")
MyArr = Array("WINGS")

'Search Column or range
With ws.Range("C:C")

    For I = LBound(MyArr) To UBound(MyArr)

        'If you want to find a part of the rng.value then use xlPart
        'if you use LookIn:=xlValues it will also work with a
        'formula cell that evaluates to "ron"

        Set Rng = .Find(What:=MyArr(I), _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)

        If Not Rng Is Nothing Then
        
        FirstAddress = Rng.Address
            Do
                'mark the cell in the column to the right if "WING" is found
                'Rng.Offset(1, 3).Value = "X"
                
            Select Case Me.TextBox6.Value
                    
                Case ("Transit")
                Rng.Offset(1, 3).Value = "550mm"
                
                Case ("Sprinter")
                Rng.Offset(1, 3).Value = "550mm"
                
                Case ("Master")
                Rng.Offset(1, 3).Value = "465mm"
                
                Case ("Movano")
                Rng.Offset(1, 3).Value = "465mm"
                
                Case ("NV400")
                Rng.Offset(1, 3).Value = "465mm"
                
                Case ("Boxer")
                Rng.Offset(1, 3).Value = "465mm"
                
                Case ("Ducato")
                Rng.Offset(1, 3).Value = "465mm"
                
                Case ("Relay")
                Rng.Offset(1, 3).Value = "465mm"
                
                End Select
        Set Rng = .FindNext(Rng)
            Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
        End If
    Next I
End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

Bjorn M Bjorn M · Accepted Answer · 2021-03-03T19:21:10

I came up with the following solution:

Private Sub TextBox6_Change()
    Dim FirstAddress As String
    Dim MyArr As Variant
    Dim Rng As Range
    Dim I As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Search for a Value Or Values in a range
    'You can also use more values like this Array("Dove", "Sky")
    MyArr = Array("WINGS")

    'Search Column or range
    With Sheets("Blad1").Range("C:C")

        For I = LBound(MyArr) To UBound(MyArr)

            'If you want to find a part of the rng.value then use xlPart
            'if you use LookIn:=xlValues it will also work with a
            'formula cell that evaluates to "ron"

            Set Rng = .Find(What:=MyArr(I), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

            If Not Rng Is Nothing Then
            
            FirstAddress = Rng.Address
                Do
                    'mark the cell in the column to the right if "WING" is found
                    'Rng.Offset(1, 3).Value = "X"
                    
                Select Case Me.TextBox6.Value
                        
                    Case ("Transit")
                    Rng.Offset(1, 3).Value = "550mm"
                    
                    Case ("Sprinter")
                    Rng.Offset(1, 3).Value = "550mm"
                    
                    Case ("Master")
                    Rng.Offset(1, 3).Value = "465mm"
                    
                    Case ("Movano")
                    Rng.Offset(1, 3).Value = "465mm"
                    
                    Case ("NV400")
                    Rng.Offset(1, 3).Value = "465mm"
                    
                    Case ("Boxer")
                    Rng.Offset(1, 3).Value = "465mm"
                    
                    Case ("Ducato")
                    Rng.Offset(1, 3).Value = "465mm"
                    
                    Case ("Relay")
                    Rng.Offset(1, 3).Value = "465mm"
                    
                    End Select
            Set Rng = .FindNext(Rng)
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            End If
        Next I
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Trying to find a value in a specified column

1 Answers