Type Mismatch error when using Index, and Match

[silenti]

System: Win XP
Excel: 2003

I´m just learning how to write Macros. What I want to do is get the corresponding category for certain fields (Mnemonic) these fields are grouped by countries. The categories are on column 3, whereas the mnemonics are grouped by countries in the rest of the columns.

The problem is I keep getting Type Mismatch error 13 in the line that has the Index and match functions. I already tried changing Mnemonic and IndicatorCat to strings to no avail.

Thanks,

Sub GenSeriesCategory()

Dim BbmRange As Range
Dim BbField As Variant
Dim WsNameB As String
Dim InitialRow As Long
Dim FinalRow As Long
Dim bbMCol As Long
Dim catRange As Range
Dim Mnemonic As Variant
Dim IndicatorCat As Variant

'Country name is the same name as the worksheet
WsNameB = ActiveSheet.Name

'Initial and final rows are taken from cells.
InitialRow = Sheet3.Range("B21").Value
FinalRow = Sheet3.Range("C21").Value
'Selects appropriate column to choose, where mnemonic is found
bbMCol = Application.Match(WsNameB, Range("'[List.xls]Mnemonics'!A2:Y2"), 0)
'Sets the ranges for the correct mnemonic country group and the fixed category group
Workbooks("List.xls").Worksheets("Mnemonics").Activate
    Set BbmRange = Workbooks("List.xls").Worksheets("Mnemonics").Range(Cells(InitialRow, bbMCol), Cells(FinalRow, bbMCol))
    Set catRange = Workbooks("List.xls").Worksheets("Mnemonics").Range(Cells(InitialRow, 3), Cells(FinalRow, 3))

ThisWorkbook.Activate
Range("B2").Select

    'This section generates all the categories for each of the Mnemonics

    For Each Mnemonic In Range("B5", Selection.End(xlToRight))
        IndicatorCat = Application.WorksheetFunction.Index(catRange, _
           Application.Match(Mnemonic, BbmRange, 0), 1)
        ActiveCell.Formula = IndicatorCat & ""
        ActiveCell.Offset(0, 1).Select
    Next
End Sub

 

[Special-K99]

Just a guess...

Should

"Application.Match"

be

"Application.WorksheetFunction.Match"

??

 

[silenti]

Already tried that. It works the same way. I´ve dropped the worksheetfunction since I´ve read it tends to be more unstable.
Thanks, Any other ideas?

 

[JoeMo]

Or:

IndicatorCat = WorksheetFunction.Index(catRange, _ 
WorksheetFunction.Match(Mnemonic.Value, BbmRange, 0), 1)

 

[silenti]

That gives me a "Runtime 1004 error: Unable to get the Match Property of the WorksheetFunction class."

 

[JoeMo]

That gives me a "Runtime 1004 error: Unable to get the Match Property of the WorksheetFunction class."

Try Dim Mnemonic as Range rather than variant.

 

[silenti]

I get the same Mismatch type error

 

[JoeMo]

I get the same Mismatch type error

The mismatch is occurring because you are asking for an exact match and there are cells in the range Mnemonic where there is no exact match. In that case, IndicatorCat is nothing.
Try this:

If Not WorksheetFunction.IsNa(WorksheetFunction.Match(Mnemonic.Value, BbmRange, 0)) Then
IndicatorCat = ... 'rest of code
End If

 

[JoeMo]

Ignore my last post and try this:

 'This section generates all the categories for each of the Mnemonics
    For Each Mnemonic In Range("B5", Selection.End(xlToRight))
        On Error Resume Next
        IndicatorCat = WorksheetFunction.Index(catRange, _
        WorksheetFunction.Match(Mnemonic.Value, BbmRange, 0), 1)
        If Not IsError(InicatorCat) Then
            ActiveCell.Formula = IndicatorCat & ""
            ActiveCell.Offset(0, 1).Select
        End If
    Next
End Sub

 

[VoG]

I would dimension the variables as Variant and use Application.Match rather that WorksheetFunction.Match. Here's why http://www.dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/