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

[KennyA]

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.



' 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



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_- (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_-)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7EqbG23kg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7KvJXmK 8o)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=Ugw3nF0C04AGt73H1BB4AaABAg.9h6VhNCM-DZ9h7E1gwg4Aq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgywFtBEpkHDuK55r214AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79hNGvJ bu)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79YAfa2 4T)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h79M1SYH 1E)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78SxhXT nR)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

Try using this


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]

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]

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]

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

[snb]

remove:

Dim Val1 As String
Dim Val2 As String

Option explcit

[KennyA]

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]

It is now working.
Option explicit


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]

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