Error 2015 - Application.Match

[gmcgough600]

Hi,

Looking for some advice on what could cause the variable exclude_yes_no to always be "Error 2015", doesn't seem to matter what is contained in the lookup value and lookup array cell(s).

Thanks in advance!

For info above this code I have code which sets current_selected_row_title_exc, last_row_exclusions_sheet etc to the correct cell row numbers and have checked this.

    current_selected_row_title_exc = Range("B" & current_row_input_sheet)
    For current_row_exclusions = 1 To last_row_exclusions_sheet
        Sheets("Exclusions").Select
        exclude_yes_no = Application.Match(Cells(current_row_exclusions, 1), current_selected_row_title_exc, 0)
        'With Sheets(“Input”).Range("B" & current_row_input_sheet)
        'Set Rng = .Find(What:=“Amazon”)
        'End With
        Sheets("Input").Select
        'Add 'exclusion' as reason for moving
    Next current_row_exclusions

 

[offthelip]

change this line:

        exclude_yes_no = Application.Match(Cells(current_row_exclusions, 1), current_selected_row_title_exc, 0)

to

        exclude_yes_no = Application.Match(Range(Cells(current_row_exclusions, 1), Cells(current_row_exclusions, 1)), current_selected_row_title_exc, 0)

 

[RoryA]

Why are you using match when the lookup "table" is one cell? It won't work, and seems pretty pointless.

 

[gmcgough600]

That makes perfect sense, thanks both! Still getting error 2015 though, what does this error actually mean? Even with an exact match I'm still getting the error, I've added watches to confirm that both Cells(current_row_exclusions, 1) and current_selected_row_title_exc contain the same string.

 

[RoryA]

Again, why are you using match at all? Just use =

 

[Alex Blakenburg]

That makes perfect sense, thanks both! Still getting error 2015 though, what does this error actually mean? Even with an exact match I'm still getting the error, I've added watches to confirm that both Cells(current_row_exclusions, 1) and current_selected_row_title_exc contain the same string.

As Rory pointed out what you are doing doesn't really make sense but to address your query......
The match function can handle a Range or an Array in the 2nd position

=match (lookup_value, lookup_array, match_type)

What it can't handle is a string in the lookup_array position.

I think it is likely that you are not declaring your variables which means that current_selected_row_title_exc is effectively a variant.
You are assigning a range to the variable without starting the line with "Set", if there are were multiple cells the variant variable would become an array and useable in the Match function. However with current_selected_row_title_exc = Range("B" & current_row_input_sheet) you are assigning a single cell to the variable and it does not become an array but a single value in this case a String.

So lets assume
lookup_value > Range(Cells(current_row_exclusions, 1), Cells(current_row_exclusions, 1)) = "Test"
lookup_array > Range("B" & current_row_input_sheet) = "Test"

So while
Application.Match("Test", Range("B" & current_row_input_sheet), 0) would work, the line
current_selected_row_title_exc = Range("B" & current_row_input_sheet)
makes current_selected_row_title_exc ="Test" a string value resulting in the expression becoming
Application.Match("Test", "Test", 0)
This produces #VALUE which in VBA = Error 2015.

 

[gmcgough600]

Hi Alex, thank you very much, looks like it was a very silly error on my part as I simply had the lookup_value and lookup_array the wrong way round! It's now giving me the expected results but only where there's an exact match, I was expecting a partial match to work e.g. lookup_value = "*Test*" and lookup_array = "1234 Test 5678" but I'm now getting error 2042 for anything thats not an exact match. I'll try declaring the variables correctly if you still advise that?

Also for clarity let me explain that the reason lookup_array is a single cell is just for testing purposes (I was going to vary this later but now looks like a loop can acheive what I want as the rest of my code rellies on this, probably not the most efficient way but should work!)

Thanks again for the detailed answer.

 

[Alex Blakenburg]

For a partial match there are a number of options:
In a loop you could use like.
If InStr(1, Multiple_Words, searchstring, vbTextCompare) > 0 Then
If Multiple_Words Like "*" & searchstring & "*" Then ' Add UCase to both sides to make it not case senstive

Using Match without a loop
If Not IsError( Application.Match("*" & searchstring & "*", lookup_array, 0)) Then

Note:Error 2042 is the VBA version for not found in lookup_array

Yes most of use prefer to use Option Explicit andd declare the variable. It gives you intellisense on the variable saving typing and helps with avoiding typos and usage errors.