Application.Match and Error '13'

[MarsBars]

Good Morning Everyone,

I'm trying to use "match" to find a row value. I've been fighting with this for about 45 minutes and don't seem to be getting anywhere. Here is the code:

ActiveWorkbook.Worksheets(1).Select

Dim a As String, B As Range

a = "MvT"

Set B = Worksheets(1).Range(Cells(1, 3), Cells(40, 3))

Dim i As Integer

i = (Application.Match(a, B, 0)) + 2


The line in bold is where the code breaks and the error is thrown. It is Error 13: Type mistmatch. I'm sure it is simple, but I guess I can't see the forest through for the trees.

Thanks in advance.

 

[Snakehips]

MarsBars,

Try ******

ActiveWorkbook.Worksheets(1).Select

Dim a As String, B As Range


a = "MvT"


Set B = Worksheets(1).Range(Cells(1, 3), Cells(40, 3))


Dim i As Integer


i = Application.Match(a, B, 0) + 2  '********

Hope that helps.

 

[hippiehacker]

and this?

Sub MarsBars()
Dim a As String, B As Range

a = "MvT"

Set B = Sheets("Sheet1").Range("C1:C40")

Dim i As Integer

i = WorksheetFunction.Match(a, B, 0)

End Sub

 

[MarsBars]

OK. So between the two of you, there is no longer an error 13. However, I now get error 1004 "unable to get the match property of the WorksheetFunction Class". Is it possible that it isn't finding the value in the range? I know that it is there. could it be a formatting issue?

 

[Norie]

It is possible that Match isn't finding the value in the range, but that wouldn't be down to a formatting problem.

Check the data in the range for leading/trailing spaces and any other non-printing characters.

Dim a As String, B As Range
Dim Res A Variant
Dim I As Long

    a = "MvT"

    With Worksheets(1)
        Set B = Worksheets(1).Range(.Cells(1, 3), .Cells(40, 3))
    End With

    Res = Application.Match(a, B, 0)

    If Not IsError(Res) Then
        I = Res+2
    End If

 

[MarsBars]

Norie, You're changes worked. However, a later part of my code is now throwing an error:1004 Application-Defined or object-defined error
ActiveWorkbook.Worksheets(1).Select
Dim a As String, B As Range
Dim Res As Variant
Dim i As Integer
a = "MvT"
With Worksheets(1)
Set B = Worksheets(1).Range(.Cells(1, 3), .Cells(40, 3))
End With
Res = Application.Match(a, B, 0)
If Not IsError(Res) Then
i = Res + 2
End If

Do While Cells(i, 2) <> "" Or Cells(i + 1, 2) <> "" Or Cells(i + 3, 2) <> ""
If Cells(i, 7) <> "" Then
Dim x As String
x = Cells(i, 2).Value
Do While Cells(i + 1, 2) <> ""
Cells(i + 1, 1).Value = x
i = i + 1
Loop
Else
i = i + 1
End If
Loop

The break is at the line with the red text. Also, please let me know if this should be a new thread. I'm still new and a little fuzzy on the rules.

 

[MarsBars]

UPDATE: It now works. Excel was doing something strange, and wouldn't let me run a macro until I closed it out and restarted the program.
Thanks for the help!