Variable not allowing Match function to work? VBA

[Oregon92]

Hi there,

I wrote a script that connects to SAP and executes different tasks. A lot of the code is related to various SAP transactions, fields and functions, so the code won’t make much sense or be particularly useful. However, I will share the code that is giving me trouble and do my best to explain how it’s reacting, versus the way I hoped it would react.

1. The first thing I am doing is identifying a size from a table in SAP and assigning that value to variable “c"

Dim c As Variant
'I can’t declare “c” as an integer because the size run is also sometimes apparel sizing

• I wrote a piece of the code that pertains to pulling a value out of SAP
• It identifies what grid value (aka size) is being displayed in SAP’s Schedule line grid
• Ex: when I run this code through a loop, the first iteration in the attached screenshot identifies a value of 7

1. I then take the value of variable “c” and try to find a match in my excel, within column C, using the code below

If IsError(Application.Match(c, Range("C:C"), 0)) Then

• The formula is trying to find size 7 in this Size column
  • If it does, I have it set to perform a particular action
  • If it doesn’t, I have it set to perform a different action

When I run my code against an apparel size run (S, M, L, XL, etc.) it functions as expected. When I run it against a Footwear size run, however, it doesn’t perform how I would expect it to. I figured out why it’s happening, but I’m not sure what my alternatives are.

The "c" variable is causing quotation marks, which isn’t identifying as a match with size 7 (because 7 is missing quotation marks). Because of this, my match function isn’t finding a match (“7” vs 7 is being treated as independent values, as opposed to being identical). In the screenshot below, for the purposes of this explanation, I created a variable called “pivot” to represent the size 7 that falls in my column C range

Is there a way to identify the two values, “7” and 7 as one of the same? Is there anyway I can turn all the values in column C into a string? I think even if they are numbers, numbers can still be read as a string.

For what it's worth, the format type of all the cells in Excel are General. I tried changing column c to the Text format, but that didn't do anything. Also, and this won't work because I am using a pivot table, but if I wasn't, I couldn't put an apostrophe in front of the size ‘7 which stored the number as text (gave it that little green triangle in the top left corner of the cell). It then recognized the cell as “7” instead of 7, and created a match. Although this isn't an option, I thought that maybe the context of it being successful might help to spark a potential solution.

I would be super grateful for anybody's help!

 

[Akuini]

Untested, but try something like this:

If IsNumeric(c) Then c = CDbl(c)
If IsError(Application.Match(c, Range("C:C"), 0)) Then

 

[Akuini]

Another option would be using Find method, it will work whether c is numeric or string.

Dim F As Range

    Set F = ActiveSheet.Range("C:C").Find(What:=c, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not F Is Nothing Then
        'DO SOMETHING
    Else
        'DO SOMETHING ELSE
    End If

 

[Oregon92]

Untested, but try something like this:

If IsNumeric(c) Then c = CDbl(c)
If IsError(Application.Match(c, Range("C:C"), 0)) Then

Thank you very much, this worked! If I understand what you did here, you are saying that if "c" is a number, convert it to a "double" variable?

Another option would be using Find method, it will work whether c is numeric or string.

Dim F As Range

    Set F = ActiveSheet.Range("C:C").Find(What:=c, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not F Is Nothing Then
        'DO SOMETHING
    Else
        'DO SOMETHING ELSE
    End If

Because the first suggestion worked, I didn't test your second suggestion (the Find method). Is there any reason you would recommend this option, over the first option? Any advantage I might be missing out on in choosing one option over another? Really appreciate your help again with this, I was at a loss for how to resolve this issue!

 

[Akuini]

Is there any reason you would recommend this option, over the first option? Any advantage I might be missing out on in choosing one option over another?

In your case, I don't see any advantages of using the second options.