Run Time error 13

[Darren Smith]

This code below says Run-time error 13 on this row

Set Tb = Body_And_Vehicle_Type_Form.TextBox6.

Can some somebody say why?

Sub Wing_Stay_Length()

Dim Tb As TextBox
Dim ws As Worksheet

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

Select Case Tb.Value

Case ("Transit")
ws.Range("G24").Value = "550mm"

Case ("Sprinter")
ws.Range("G24").Value = "550mm"

Case ("Master")
ws.Range("G24").Value = "465mm"

Case ("Movano")
ws.Range("G24").Value = "465mm"

Case ("NV400")
ws.Range("G24").Value = "465mm"

Case ("Boxer")
ws.Range("G24").Value = "465mm"

Case ("Ducato")
ws.Range("G24").Value = "465mm"

Case ("Relay")
ws.Range("G24").Value = "465mm"

End Select

End Sub

 

[Darren Smith]

I`ve sent the code over to you rather than the whole workbook.
When I try to run it the Set MyCell code says "Out of context"?

Private Sub TextBox6_Change()

    Dim ws As Worksheet
    Dim MyCell As Range
    
    Set ws = ThisWorkbook.Worksheets("Job Card Master")
    Set MyCell = ws.Columns("C").Find("Wings", , xlValues, xlWhole, xlByRows, xlNext, False).Offset(1, 3)
    If Not MyCell Is Nothing Then
    
Select Case Me.TextBox6.Value
    
Case ("Transit")
MyCell.Value = "550mm"

Case ("Sprinter")
MyCell.Value = "550mm"

Case ("Master")
MyCell.Value = "465mm"

Case ("Movano")
MyCell.Value = "465mm"

Case ("NV400")
MyCell.Value = "465mm"

Case ("Boxer")
MyCell.Value = "465mm"

Case ("Ducato")
MyCell.Value = "465mm"

Case ("Relay")
MyCell.Value = "465mm"

End Select

End If

End Sub

 

[Darren Smith]

Also asked Stack overflow

Log In

Stack Overflow | The World’s Largest Online Community for Developers

stackoverflow.com

 

[Fluff]

That link is to the login page of Stack, you need to supply a link to your actual post.

 

[Darren Smith]

Trying to find a value in a specified column

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 ...

stackoverflow.com

 

[mohadin]

Try

Private Sub TextBox6_Change()
    Dim ws As Worksheet
    Dim Wpos As Range
    Set ws = ThisWorkbook.Worksheets("Job Card Master")
    Set Wpos = ws.Columns("C").Find("Wings", , xlValues, xlWhole, xlByRows, xlNext, False).Offset(1, 4)
    Select Case Me.Value
    Case ("Transit")
        Wpos = "550mm"
    Case ("Sprinter")
        Wpos = "550mm"
    Case ("Master")
        Wpos = "465mm"
    Case ("Movano")
        Wpos = "465mm"
    Case ("NV400")
        Wpos = "465mm"
    Case ("Boxer")
        Wpos = "465mm"
    Case ("Ducato")
        Wpos = "465mm"
    Case ("Relay")
        Wpos = "465mm"
    End Select
End Sub

 

[Darren Smith]

I`ve redone the code below but it is still not working?
As in the value does not fill in to G column
Please can somebody help

Private Sub TextBox6_Change()
    Dim FirstAddress As String
    Dim MyArr As Variant
    Dim Rng As Range
    Dim ws As Worksheet
    Dim I As Long
    Dim x 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
                  
                    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

 

[MARK858]

Column G is 4 columns across not 3

 

[Darren Smith]

Thanks for that. How can I change it from being X in the right cell to the case selection below?

 

[mohadin]

set Rng=Rng.Offset(1, 4)

Select Case Me.TextBox6.Value

Case ("Transit")
Rng = "550mm"

Case ("Sprinter")
Rng = "550mm"

Case ("Master")
Rng= "465mm"

Case ("Movano")
Rng= "465mm"

Case ("NV400")
Rng = "465mm"

Case ("Boxer")
Rng = "465mm"

Case ("Ducato")
Rng = "465mm"

Case ("Relay")
Rng. = "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

 

[Darren Smith]

Sorry but this turned yellow it still shows the right value.

Set Rng = .FindNext(Rng)