Again another Run Time Error 13

[HYKE]

Hi There,

I have made a new userform (userform4) and assigned it to sheet 8. However, the code below is giving me a runtime error 13. I have tried naming the userform4.combobox1 but still is giving me the same error. What could be the reason behind now?
Dim r As Long
With Sheet8
r = Application.Match(UserForm4.ComboBox1.Value, .Range("F:F"), False)

.Cells(r, 17).Value = TextBox2.Value
.Cells(r, 18).Value = TextBox3.Value
.Cells(r, 19).Value = TextBox4.Value
.Cells(r, 29).Value = TextBox5.Value
.Cells(r, 30).Value = TextBox6.Value



TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""


Rows(r).Select
UserForm4.ComboBox1.SetFocus

End With
'ErrHandler:
'If Err.Number = 13 Then
' MsgBox "No Data to Display on System Stock!", vbExclamation, "Update Sheet"
' End If
End Sub

 

[HYKE]

re-posting the code

 Dim r As Long
    With Sheet8
        [COLOR=red] r = Application.Match(UserForm4.ComboBox1.Value, .Range("F:F"), False)
[/COLOR]        
        .Cells(r, 17).Value = TextBox2.Value
        .Cells(r, 18).Value = TextBox3.Value
        .Cells(r, 19).Value = TextBox4.Value
        .Cells(r, 29).Value = TextBox5.Value
        .Cells(r, 30).Value = TextBox6.Value
        
        
       
        TextBox2.Value = ""
        TextBox3.Value = ""
        TextBox4.Value = ""
        TextBox5.Value = ""
        TextBox6.Value = ""
        
        
        Rows(r).Select
        UserForm4.ComboBox1.SetFocus
       
    End With
'ErrHandler:
    'If Err.Number = 13 Then
   ' MsgBox "No Data to Display on System Stock!", vbExclamation, "Update Sheet"
   ' End If
End Sub

 

[schielrn]

What is the value in the combobox? Is it a number?

 

[HYKE]

it can be numbers and letters too..

 

[schielrn]

Are you getting an error on letters? I could see maybe an issue where it is trying to match a number with a text number and that would not match and could produce that error. Does this work?

r = Application.Match(clng(UserForm4.ComboBox1.Value), .Range("F:F"), False)

But again this can only be used for numbers.

 

[HYKE]

Hi,

Yes your code works with number, but is not with text and numbers. Is it possible to code to work for both?

Thanks,

 

[schielrn]

I would format column F as text. You may need to use text to columns to actually set it as text.

Otherwise you could do something like:

if isnumeric(UserForm4.ComboBox1.Value) then
r = Application.Match(clng(UserForm4.ComboBox1.Value), .Range("F:F"), False)
else
r = Application.Match(UserForm4.ComboBox1.Value, .Range("F:F"), False)
end if

Hope that helps.

 

[HYKE]

This is working fine and of great help. thanks..