# Match Function In Vba Returning Error

• Hi there,
I am trying to use a match function in VBA to return a value when a number of values in a row match a certain criteria (I have tried the Find approach on the Ozgrid help pages but it is not what I am after). My problem using a match function of course is that when there is no match the code returns a type mismatch error. I have tried to circumvent the error problem using an "On error resume next" but this then delivers a match for all rows. Anyone have any thoughts about how I can get around this problem?

• Re: Match Function In Vba Returning Error

That code does not look right you should be using application.worksheetfunction.match

But Match is designed for finding values in a range/array. You are only specifiying to look in one cell?

You should give us a better explanation of what you are trying to do.

• Re: Match Function In Vba Returning Error

When he writes:

Code
``Range("\$C\$4).Offset(i,0)``

his he attempting to say look for the value in the range \$C\$4:Ci?

If that was his intention, what is he doing wrong?

Regards!

• Re: Match Function In Vba Returning Error

Quote from samuels90

When he writes:

Code
``Range("\$C\$4).Offset(i,0)``

his he attempting to say look for the value in the range \$C\$4:Ci?
Regards!

For starters you dont need the \$.

If i = 2 then This code

Code
``Range("C4).Offset(i,0)``

refers to the SINGLE cell C6. There is no point in using match for a single cell you may as well use:

Code
``If range("C4").Offset(i,0).value = "A" then``

If you want a range say: C4 to C and i rows, then:

Code
``Range("C4", "C" & i)``
• Re: Match Function In Vba Returning Error

Thank you for your patience. Maybe it is because I am not good at math why I did not get your code;

Code
``If range("C4").offset(i,0).value="A" Then``

it does not make enough sense to me.

I am not saying that bbromley code is right, but his logic may be. Your suggestion;

Code
``Range("C4", Range("C" & i))``

may not be appropriate when i=1,2,3 or even 4, based on your argument.

It would be more appropriate if bbromley make his own case, but don't be afraid to school me if I just plain crazee.

No problem!

• Re: Match Function In Vba Returning Error

Samuels90,

No problem at all I dont mind explaining.

Simply what the Op has coded:

Code
``Range("C4").Offset(i, 0)``

Refers to a SINGLE cell. Not a range of cells. In can be written as:

Code
``Cells(4,3).Offset(i, 0)``

There is no point in using worksheetfunction.match on a single cell. My if statement was only an example instead of using match on a single cell which is what was coded. I wasnt presuming that was what the Op wanted I was just explaining what the code was doing.

From the help file:
MATCH
Returns the relative position of an item in an array that matches a specified value in a specified order.

My suggestion for:

Code
``Range("C4","C" & i)``

was only based on your comment:

Quote

he attempting to say look for the value in the range \$C\$4:Ci?

Anyway lets just see exactly what the op wants and then I can give some code which will help.

• Re: Match Function In Vba Returning Error

Samuels90, please start you own thread if you have questions and don't hijack the thread of another. It is in the rules you agreed to.

Quote

You should give us a better explanation of what you are trying to do.

That should have be done via the Thread Title. The OP is now locked into ONLY fixing a VBA MATCH error.

• Re: Match Function In Vba Returning Error

Just for clarity, the reason I have chosen to use the Match function on a single cell is that it will return either 1 or N/A which means that I can multiply the product of the Matches for each of the three cells in the row and if all criteria are met then I have a positive result. When I get a negative result I get an Error result the macro crashes instead of simply rolling onto the next row and checking for a positive result.
So to be clear as to my question, I was hoping that someone would be able to tell me how I can get the code to skip past an error reult from the Match functions and continue with the loop. I have tried using an On Error Resume Next but that results in the error being treated as a positive result.

• Re: Match Function In Vba Returning Error

bbromley

Since you are using Match Method (not function), it will return Error as a result (not runtime error).
Therefore, when error returned,

Code
``myTotal = myVal1 * myVal2 * myVal3``

Above line should crash...[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Quote from bbromley

Just for clarity, the reason I have chosen to use the Match function on a single cell is that it will return either 1 or N/A which means that I can multiply the product of the Matches for each of the three cells in the row and if all criteria are met then I have a positive result. When I get a negative result I get an Error result the macro crashes instead of simply rolling onto the next row and checking for a positive result.
So to be clear as to my question, I was hoping that someone would be able to tell me how I can get the code to skip past an error reult from the Match functions and continue with the loop. I have tried using an On Error Resume Next but that results in the error being treated as a positive result.

try

Code
``````If (InStr("A"m Range("C4").Offset(i))) * (InStr("B", Range("F4").Offset(i))) * (InStr("C", Range("I4").Offset(i))) Then
' you code for True
End If``````

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!