Why is this code returning a Runtime error 13 Type mismatch?

**KennyA** · 08-28-2015, 09:41 PM

This macro is looking at two cells (A8 and A10) to determine if the values are there in both cells or only one cell.
If the values are in both cells C11 get a value of 2
If the value is in only one cell C11 gets a value of 1
I do need to add a third option where if the valuse in the code below are not found in either cell C11 gets a value of 0.

The text in red in the code, shows where the error occures.
All cells on Sheet1 and PART are formatted as General. There are no extra spaces or characters in the cells where the data is being pulled from. I am stumped and I hope someone can help.

Code:

 ' Part quantity being determined
        Dim Val1 As String
        Dim Val2 As String
        Val1 = Sheet1.Range("A8")
        Val2 = Sheet1.Range("A10")
        If Val1 = "-A" Or "-B" Or "-H" _
        And Val2 = "-A" Or "-B" Or "-H" Then
            Sheet1.Range("C11").Value = 2
            Sheets("PART").Range("B35:E35").Copy Destination:=Sheet1.Range("A11")
        Else
            Sheet1.Range("C11").Value = 1
        
        End If

**Excel Fox** · 08-29-2015, 01:27 AM

Try using this

Code:

Sub T()

 ' Part quantity being determined
    Dim Val1 As String
    Dim Val2 As String
    If IsEmpty(Sheet1.Range("A8")) Then
        Val1 = ""
    Else
        Val1 = Sheet1.Range("A8").Text
    End If
    If IsEmpty(Sheet1.Range("A10")) Then
        Val2 = ""
    Else
        Val1 = Sheet1.Range("A10").Text
    End If
    If (Val1 = "-A" Or Val1 = "-B" Or Val1 = "-H") And (Val2 = "-A" Or Val2 = "-B" Or Val2 = "-H") Then
        Sheet1.Range("C11").Value = 2
        Sheets("PART").Range("B35:E35").Copy Destination:=Sheet1.Range("A11")
    Else
        Sheet1.Range("C11").Value = 1
    End If
        
End Sub

**KennyA** · 08-29-2015, 03:51 AM

Thank you for your reply. I tried the code you provided but no matter the combinations I put in A8 and A10, C11 always returns a value of 1.
I am curious about why the code I posted was causing the type mismatch though. I have been working on just this little bit of code for two weeks with little success.

You help is appreciated

**Excel Fox** · 08-29-2015, 06:44 AM

So now you don't have an error code. But the logic seems yo be not working, right?

Can you post a sample file

**KennyA** · 08-31-2015, 07:04 PM

Do you wnat the Excel file or just the code for the User form?

**snb** · 08-31-2015, 07:55 PM

remove:

Dim Val1 As String
Dim Val2 As String

Option explcit

**KennyA** · 08-31-2015, 08:56 PM

I removed:
Dim Val1 As String
Dim Val2 As String

I did not see Option explcit in the code so I could not remove it. I ran through the program and still the value in C11 returns 1 and not 2 when it should.
Also tried adding Option explcit and I recieved a compile error. "Expected:Base or Compare or Explicit or Private"

I am very new to writing macros so do I have to do or add something other than just Option explcit

**KennyA** · 09-01-2015, 12:47 AM

It is now working.
Option explicit

Code:

  Dim Val1 As String
    Dim Val2 As String
  
        Val1 = Sheet1.Range("A8").Text
        Val2 = Sheet1.Range("A10").Text

    If (Val1 = "-A" Or Val1 = "-B" Or Val1 = "-H") And (Val2 = "-A" Or Val2 = "-B" Or Val2 = "-H") Then
        Sheet1.Range("C11").Value = 2
        Sheets("BLPVC").Range("B35:E35").Copy Destination:=Sheet1.Range("A11")
    Else
        Sheet1.Range("C11").Value = 1
    End If

Thank you for your help

**snb** · 09-01-2015, 01:50 AM

Code:

sub M_tst()
   with Sheet1
    .cells(11,3) = 2
    If instr("-A-B-H",.[A1]) And instr("-A-B-H",.[A10]) Then
        .cells(11,3) = 2
        .[A11:D11]=Sheets("BLPVC").Range("B35:E35").Value
    End If
  end with
end sub