Runtime error '13'

[buzz71023]

I have a workbook that auto filters results in another workbooks based the value of a range. The code works great when only numbers are being used but when a letter is introduced into the cell, I get a RUNTIME ERROR '13', TYPE MISMATCH debug error.

For example, the code works great if I use "512022" but if I use "512022-U" or "512022U" I get the runtime error. Code error is in red.

Private Sub CommandButton1_Click()
Dim rng As Range
Dim CAFval As Double

Application.ScreenUpdating = False

Sheets("MDF").Range("ProductCode").Value = TextBox1.Value
Sheets("Porosity").Range("f2").Value = TextBox1.Value
Sheets("Data Sheet").Range("ProductCode").Value = TextBox1.Value

'open CAF Spreadsheet and auto filter results based on product code

Application.ThisWorkbook.FollowHyperlink "P:\Lab Folder\CAF\CAF 10.xls"
 
Set rng = Workbooks("CAF 10.xls").Sheets("Main").Range("$B$5:$N$891")
[COLOR=#ff0000]Let CAFval = Workbooks("Quality Control Data Sheet (BETA).xls").Sheets("Data Sheet").Range[/COLOR]
("ProductCode").Value
       
            With rng
                .AutoFilter field:=1, Criteria1:=CAFval
        End With
Windows("Quality Control Data Sheet (BETA).xls").Activate
Application.ScreenUpdating = True
    
    If Range("ProductCode") = "10126" Or Range("ProductCode") = "10478" Or _
       Range("ProductCode") = "10561" Or Range("ProductCode") = "100041" Or _
       Range("ProductCode") = "100900" Or Range("ProductCode") = "100977" Or _
       Range("ProductCode") = "101901" Or Range("ProductCode") = "101949" Or _
       Range("ProductCode") = "102077" Or Range("ProductCode") = "102109" Or _
       Range("ProductCode") = "102131" Or Range("ProductCode") = "102282" Or _
       Range("ProductCode") = "102951" Or Range("ProductCode") = "7011837" Or _
       Range("ProductCode") = "7012112" Or Range("ProductCode") = "10073-U" Then
           MsgBox "The FILM for this Product Code can be tested every 2 hours, but has to be approved by KYLE CLAY or RICK KNIGHT.", vbInformation
    End If
    
    If Range("ProductCode") = "110080" Or Range("ProductCode") = "11078" Or _
       Range("ProductCode") = "11078-R" Or Range("ProductCode") = "111818" Or _
       Range("ProductCode") = "112147" Or Range("ProductCode") = "7180674" Or _
       Range("ProductCode") = "7188175" Or Range("ProductCode") = "7188335" Or _
       Range("ProductCode") = "7188338" Or Range("ProductCode") = "7188763" Or _
       Range("ProductCode") = "511024" Or Range("ProductCode") = "111391" Or _
       Range("ProductCode") = "11070" Then
        Chips.Show
    End If
    
    
    
Unload Me
LotNumber.Show
End Sub

 

[Trebor76]

The code works great when only numbers are being used but when a letter is introduced into the cell, I get a RUNTIME ERROR '13', TYPE MISMATCH debug error

Correct. As you've declared the 'CAFval' variable as a double type, it can only accept numeric entries being assigned to it. If the variable is to have both numeric values and text strings assigned to it, define the variable as a variant instead, i.e.

Dim CAFval As Variant

HTH

Robert

 

[AMAS]

This should not be over two lines:

Let CAFval = Workbooks("Quality Control Data Sheet (BETA).xls").Sheets("Data Sheet").Range("ProductCode").Value

 

[buzz71023]

Thanks Trebor76, that fixed it.

 

[buzz71023]

AMAS, Thanks for the reply. I know it is not suppose to be on two lines. The code was working with everything but letters,I must have hit enter when highlighting the line. Again,thanks for the reply.

 

[buzz71023]

Trebor, may I ask what the difference is between using double and variant? I know the byte size is different anything else? I'm new to the "dim" concept. This code was actually one of my first with "dim"

 

[Trebor76]

Double variables can only hold (be assigned) negative numbers -1.79769313486232E308 to -4.94065645841247E-324 or 4.94065645841247E-324 to 1.79769313486232E308 for positive values.

Variant variables though much more flexible in that they can hold (be assigned) numeric, string, date and the special values Empty and Null, they are more expensive in programming terms to use as their flexiblity comes at a cost in that they use more memory.

Dave Hawley has a great overview of variables, their types and uses here