Type MisMatch, but can't figure out why...

[Tagis2500]

Hello all, I'm very new to VBA...

I understand that generally a type mismatch means you have a variable defined wrong. But I can't figure out what it is!

There's an Excel sheet with a grades. Test 1, Test 2, Test 3, etc.

I'm trying to calculate the final grade with specific curves with their ID.

I just need help with the Error 13 Type MisMatch. I want to figure everything else out on my own.

In the ranges they are all numbers between 0 and 100. The FinalAverage has decimal points. But that's it. It worked until I did the Else statement.

Can anyone please help?

Sub GetFinalGrade()


'Declaration of Variables
Dim strIDQuestion As String
Dim sngCurve1 As Single
Dim sngElseCurve As Single
Dim sngFinalGrade As Single
Dim sngTest1 As Single
Dim sngTest2 As Single
Dim sngTest3 As Single
Dim sngFinalAverage As Single




'Assign variables to their values
sngCurve1 = 0.05
sngElseCurve = 0.02
sngTest1 = Sheets("Fall 2016 Grades").Range("D2:D13")
sngTest2 = Sheets("Fall 2016 Grades").Range("E2:E13")
sngTest3 = Sheets("Fall 2016 Grades").Range("F2:F13")
sngFinalAverage = Sheets("Fall 2016 Grades").Range("G2:G13")


'Asks what their ID is
strIDQuestion = InputBox("What is your student ID?", "Your Grade For You")


'Elseifs to decide what final grade is
If sngTest3 > sngTest1 And sngTest2 Then
sngFinalGrade = (sngFinalAverage * sngCurve1)

Else
sngFinalGrade = (sngFinalAverage * sngElseCurve)

End If


End Sub

 

[MARK858]

Is it on sngFinalGrade (always good to say where the error occurred) and if it is have you tried changing it to double?

 

[Tagis2500]

I tried turning all the singles into doubles. I tried turning the whole numbers (test 1, test 2, and test 3) into integers too.

I don't know how to tell exactly where it occurred. All I know is it was fine until I did the first If Statements. I tried it without the else at first to test it out.

 

[JLGWhiz]

I tried turning all the singles into doubles. I tried turning the whole numbers (test 1, test 2, and test 3) into integers too.

I don't know how to tell exactly where it occurred. All I know is it was fine until I did the first If Statements. I tried it without the else at first to test it out.

If you click the 'Debug' button when the error message appears, it shows the line of code where the error occurred with a highlight. Try this modified line in you else statement and see if it works.

sngFinalGrade = [COLOR=#B22222]Csng[/COLOR](sngFinalAverage * sngElseCurve)

 

[MARK858]

Try what JLGWhiz has suggested and If there is still nothing being highlighted as an error start from the bottom and comment out blocks of code until the code runs then you will know it is the next block of code causing the error.
Dim whole numbers as Long not Integer as there is no benefit using Integer on modern computers.

 

[JLGWhiz]

If sngTest3 > sngTest1 And sngTest2 Then

This statement is invalid. When you use the And operator in a comparison statement, the item being compared must have the object being compared to specified, even if it is the same as the previous comparison. Example

If sngTest3 > sngTest1 And [COLOR=#B22222]sngTest3 >[/COLOR] sngTest2 Then

 

[Tagis2500]

Well it turns out I have a bigger problem. :/ I forget to set the code to find the test 1, test 2, test 3, and finalaverage cells that are related to the ID number. I tried to add the find, but it gave me Run Time error 91: The Object variable or with block variable not set.

This is the only code I changed.

Set sngTest1 = Sheets("Fall 2016 Grades").Range("D2:D13").Find(strIDQuestion).ActivateSet sngTest2 = Sheets("Fall 2016 Grades").Range("E2:E13").Find(strIDQuestion).Activate
Set sngTest3 = Sheets("Fall 2016 Grades").Range("F2:F13").Find(strIDQuestion).Activate
Set sngFinalAverage = Sheets("Fall 2016 Grades").Range("G2:G13").Find(strIDQuestion).Activate

Along with setting those variables As Objects, and not As Singles

Those are the codes with things wrong, I commented it out till it gave an error and four do.

Should I just start over? :/

 

[Tagis2500]

Not sure if this is how you do it, but I did debug.Print "Set sngTest1 = Sheets("Fall 2016 Grades").Range("D2:D13").Find(strIDQuestion).Activate"
and it said that the sub or function was not defined...

 

[JLGWhiz]

Well it turns out I have a bigger problem. :/ I forget to set the code to find the test 1, test 2, test 3, and finalaverage cells that are related to the ID number. I tried to add the find, but it gave me Run Time error 91: The Object variable or with block variable not set.

This is the only code I changed.



Along with setting those variables As Objects, and not As Singles

Those are the codes with things wrong, I commented it out till it gave an error and four do.

Should I just start over? :/

If you want to use the find statement to initialize your Test1, 2 and 3 variables, then Dim them as Range. You don't need the .Activate at the end of the find function. When you initialize the variable it become the same entity as the full Range() description and you can use it in code in the same manner as using a Range object. Example

Dim newVariable as Range
Set sngTest1 = Sheets("Fall 2016 Grades")/Range("D2:D13")
Set newVariable = sngTest1.Find(strIDQuestion, , xlValues, xlWhole) 'This returns the location of strIDQuestion
Dim newVariable As Long
newVariable = sng(Test1.Find(strIDQuestion, , xlValues, xlWhole).Value 'this returns the value of the strIDQuestion location.

 

[Tagis2500]

If you want to use the find statement to initialize your Test1, 2 and 3 variables, then Dim them as Range. You don't need the .Activate at the end of the find function. When you initialize the variable it become the same entity as the full Range() description and you can use it in code in the same manner as using a Range object. Example

Dim newVariable as Range
Set sngTest1 = Sheets("Fall 2016 Grades")/Range("D2:D13")
Set newVariable = sngTest1.Find(strIDQuestion, , xlValues, xlWhole) 'This returns the location of strIDQuestion
Dim newVariable As Long
newVariable = sng(Test1.Find(strIDQuestion, , xlValues, xlWhole).Value 'this returns the value of the strIDQuestion location.

Thank you! I have a few questions though. Why the divide symbol? And how many new variables is that? I can't really tell...