Object Required in my Function to match an array

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
169
I have a function (taken from here) to determine if a value appears in an array.

The value I'm looking for is the last entry in an array, in box 6.

I have coded as follows:

VBA Code:
If ExportArray(UBound(ExportArray), 6) <> "" Then

    If IsInArray(ExportArray(UBound(ExportArray), 6).Value, CustomerData) = False Then
        MsgBox ("Customer Not Found")
    Else
        MsgBox ("Customer Found")
    End If

End If

The first line is because the table is blank and so I wanted to skip if this is the case.

My function looks like this:

VBA Code:
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

I get an Object required error at the "If IsInArray" line.

Which obvious error have I made?
Thanks,!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

TheRedCardinal

Board Regular
Joined
Jul 11, 2019
Messages
169
I "fixed" the issue by ascribing the value from the array as its own variable:

VBA Code:
    SearchStr = ExportArray(UBound(ExportArray), 6)

    If IsInArray(SearchStr, CustomerData) = False Then

I'd love to understand how to make this unneeded though?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need to remove the .Value from your first code. Arrays don't have properties like Value.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,343
Members
417,021
Latest member
moon miner

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