Suddenly runtime error 13 for no evident reason

Question

I'm using a modified match function since years which worked fine. But suddenly I get runtime error 13 for no reason. This function is called two times in the actual process before the error pops up. The first time everything works fine, the second time I get the error. Here's the code:

Public Function xMatch(ByRef Direction_Range As Range, ByVal Find_Value_Or_String, Occ_Number As Integer, Row_True_Or_Column_False As Boolean, RelativePosition_True_Or_AbsolutePosition_False As Boolean) As Integer
Dim xMTcell
toolVar1 = 0
xMatch = 0
occurrencesCount = 0
If RelativePosition_True_Or_AbsolutePosition_False = True Then
    If Row_True_Or_Column_False = True Then
        toolVar1 = Range(Split(Direction_Range.Address, ":")(0)).Row - 1
    Else
        toolVar1 = Range(Split(Direction_Range.Address, ":")(0)).Column - 1
    End If
End If
For Each xMTcell In Direction_Range
    If xMTcell.Value = Find_Value_Or_String Then
        occurrencesCount = occurrencesCount + 1
        If occurrencesCount = Occ_Number Then
            If Row_True_Or_Column_False = True Then
                xMatch = xMTcell.Row - toolVar1
            Else
                xMatch = xMTcell.Column - toolVar1
            End If
            Exit For
        End If
    End If
Next xMTcell
End Function

toolVar1 and occurrencesCount are declared in the module. The function can search in any ranges (Direction_Range) and find the Value (Find_Value_Or_String). In contrary to a regular match function you can decide (Occ_Number) which find will be the one you need if there are multiple ones in that range. Also, you can decide if you want the row or the column of that find and if you want that row/column absolute (position compared to worksheet) or relative (position compared to Direction_Range).

The error occurs in this line:

If xMTcell.Value = Find_Value_Or_String Then

Since xMTcell is part of Direction_Range I checked the Range and it is clearly the right one plus it IS a range and nothing else. I also checked the Value and it is a String he is looking for which can be found manually in that range. I can't understand why it works fine in other stages of the process with the exact same type of Direction_Range and Find_Value_Or_String and suddenly it doesn't. I already tried declaring xMTcell as Range but it made no difference.

Anyone an idea?

Regards Carl

---

According to the comments i did following checks:

Debug.Print VarType(Direction_Range)
Debug.Print Direction_Range.Address
Debug.Print VarType(xMTcell.Value)
Debug.Print VarType(Find_Value_Or_String)
Debug.Print xMTcell.Address

and for the non-bugging process i get

8204
$A$4:$N$4
8
8
$B$4

and for the bugging process

8204
$A$5:$A$16
8204
8
$A$5:$A$16

so its a problem with the range, why does it behave differently?

Answer 1

TL;DR:

Change For Each xMTcell In Direction_Range to For Each xMTcell In Direction_Range.Cells.

---

Some debugging and general tips (summarizing the comments and your feedback in them):

• Dim xMTcell - make that a Range.
• Use Debug.Print (Control+G to bring up the Immediate Window and inspect the output).
• Debug.Print VarType(xMTcell.Value) returns 8204: Per the VarType docs, that means that xMTcell.Value is a vbArray of vbVariants (8192 + 12 = 8204).
• Debug.Print VarType(Find_Value_or_String) returns 8: Again per the VarType docs, this means Find_Value_or_String is a String.
• The type mismatch is because you can't compare a String to an array.
• The fact that xMTCell.Value is an array points to xMTCell being a multi-cell range, not a single cell.
• ... which is verified by the output of Debug.Print xMTcell.Address being a multi-cell range.
• Most likely the issue is that you passed a Row or Column as your Direction_Range, i.e. you used Rows or Columns to return a range. When looping over a Row or Column, you need to specify that you're looping over the individual cells.