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

 

[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.

I got the variable part! So in my calculations, I would use the variable for the one returning the value, right?

 

[JLGWhiz]

I got the variable part! So in my calculations, I would use the variable for the one returning the value, right?

Well, my intent was to show you how to use the find function to either set an object variable for a range or to just get a value, without using the Activate method. It is up to you to determine what you need the variable for. The point is, if you use the set statement with the find function, it returns a range object. If you do not use the Set keyword, then you must attach a property such as .Value, .Row, .Formula, etc. to meet parameter of whatever data type you declared the variable to be. Your original statement was a compound statement, and those can be troublesome if you don't get them exactly right. We are getting way off track from the original problem of type mismatch.

 

[Tagis2500]

So sorry. Maybe not. Thank you so much for helping me! So like this:

Dim Test1 As RangeDim sngTest1 As Long

Set Test1 = Sheets("Fall 2016 Grades") / Range("D2:D13")
Set sngTest1 = Test1.Find(strIDQuestion, , xlValues, xlWhole) [Code]

When I do range on both it works without error, but it doesn't give any values to the variables. When I put long it says "object required". But then when I put it As Object and debug a line it says "sub or function not defined" for Set Test1 = Sheets("Fall 2016 Grades") / Range("D2:D13")

 

[Tagis2500]

You're definitely right. Thank you all!

 

[JLGWhiz]

So sorry. Maybe not. Thank you so much for helping me! So like this:

Dim Test1 As RangeDim sngTest1 As Long

Set Test1 = Sheets("Fall 2016 Grades") / Range("D2:D13")
Set sngTest1 = Test1.Find(strIDQuestion, , xlValues, xlWhole) [Code]

When I do range on both it works without error[COLOR=#b22222], but it doesn't give any values to the variables. When I put long it says "object required[/COLOR]". But then when I put it As Object and debug a line it says "sub or function not defined" for Set Test1 = Sheets("Fall 2016 Grades") / Range("D2:D13")[/QUOTE]

Read post # 12 very carefully on the part about attaching the properties.
[Code]
Sub t()
x = sngTest1.Value
MsgBox x
End Sub