Variable not allowing Match function to work? VBA

Oregon92

New Member
Joined
Jun 25, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Attachments

  • help.png
    help.png
    117.9 KB · Views: 8

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,982
Office Version
  1. 365
Platform
  1. Windows
Untested, but try something like this:
VBA Code:
If IsNumeric(c) Then c = CDbl(c)
If IsError(Application.Match(c, Range("C:C"), 0)) Then
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,982
Office Version
  1. 365
Platform
  1. Windows
Another option would be using Find method, it will work whether c is numeric or string.
VBA Code:
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

New Member
Joined
Jun 25, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Untested, but try something like this:
VBA Code:
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.
VBA Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,982
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,145
Messages
5,901,601
Members
434,906
Latest member
butterthemuffin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top