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


    Readify,


    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


    Readify,


    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


    Thanks for your comments everyone.
    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

    Thanks for your comments everyone.
    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!