Error running Match function in VBA

**CrystalNewb** · 12-07-2009, 01:11 PM

Any idea why I would be getting an error saying:

Unable to get the Match property of the worksheetfunction class?

Here is my code

Sub Winner()

Dim FirstScore As Double
Dim FirstPlace As String
Dim TheMatch As Integer


FirstScore = WorksheetFunction.Max(Range("GrandTotal"))
TheMatch = WorksheetFunction.Match(12, GrandTotal, 0)
FirstPlace = WorksheetFunction.Index(B1, E1, TheMatch)

MsgBox "First Place is " & FirstPlace & " with " & FirstScore & " Points.", vbOKOnly, "Reading of the Scores"

End Sub

**DonkeyOte** · 12-07-2009, 01:18 PM

You need to encase your Ranges... see FirstScore use of Range and compare that to the syntax used in TheMatch, same holds true for FirstPlace.

Also your INDEX formula does not make much sense to me... INDEX(Range,Row_Index,Column_Index)

**Jack in the UK** · 12-07-2009, 01:19 PM

GrandTotal is not defined

**CrystalNewb** · 12-07-2009, 02:03 PM

GrandTotal is a range on my worksheet.
I'm including an attachment this time with what I have so far.
I'm still getting the same Error though.

I've got a formula working on the worksheet itself but i'd really rather have a message box.

Sub Winner()

Dim FirstScore As Double
Dim FirstPlace As String
Dim TheMatch As Integer


FirstScore = WorksheetFunction.Max(Range("GrandTotal"))
TheMatch = WorksheetFunction.Match("FirstScore", "GrandTotal", 0)
FirstPlace = WorksheetFunction.Index("B1:E1", TheMatch)

MsgBox "First Place is " & FirstPlace & " with " & FirstScore & " Points.", vbOKOnly, "Reading of the Scores"

End Sub

What am I doing wrong here?