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

 

[Norie]

The usual reason for this error when working with these sort of functions is because nothing is being found.

If all you are trying to do is search for a value why not use Find?

Set rngFnd = Bbmrange.Find(What:=Mnemonic.Value)
 
If rngFnd Is Nothing Then
     Msgbox Mnemonic.Value " not found." 
Else
     ' code if the Mnemonic is found
End If

 

[JoeMo]

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/

Very interesting reference, thanks for supplying it Peter.

 

[silenti]

Thanks to all, it finally worked with this configuration.

Sub GenSeriesCategory()

Dim WsNameB As String
Dim bbMColumn As Long
Dim Mnemonic As Variant
Dim IndicatorCat As Variant
Dim FinalRow As Long
Dim InitialRow As Long
Dim BbmRange As Range
Dim catRange As Range
Dim MnemRange As Range

'Country name is the same name as the worksheet
WsNameB = ActiveSheet.Name
Range("B5").Activate
Set MnemRange = Range("B5", Selection.End(xlToRight))

'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
bbMColumn = 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, bbMColumn), Cells(FinalRow, bbMColumn))
    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 MnemRange
        On Error Resume Next
        IndicatorCat = WorksheetFunction.Index(catRange, _
        Application.Match(Mnemonic.Value, BbmRange, 0), 1)
        If Not IsError(IndicatorCat) Then
            ActiveCell.Formula = IndicatorCat
            ActiveCell.Offset(0, 1).Select
        End If
    Next
End Sub