Range.Find.Execute error "Argument Not Optional" Excel VBA

Question

my name is Joe and I am very new to Excel VBA programming. I was asked to put together a program that facilitated in the analysis of parking data at my internship, and I decided to use Excel as the base to store the data and create charts. I also decided I would need to use macros to improve the functionality and automation of the program.

I learned basic Excel VBA just to develop this sheet, so it is literally my first time writing macros in Excel.

Anyway, the sheet has data stored in columns for different time slots, and a cell at the top of the column labeling the column. The data is separated by type of land use, such as Shopping Center, Cinema, Supermarket, etc. The user is able to click a ComboBox to select a land use, and there is a macro that displays the data for that respective land use in the chart using the Range.Find method. My problem is that some of the land uses do not have data associated with them for different functionalities, and I would like to be able to know this before the Range.Find method returns an error. Therefore, I thought a simple If statement would work by incorporating the Range.Find.Execute method, which should return a Boolean value, but always returns an Error, "Argument not Optional," when run instead. The debugger always highlights "Find."

I am doing the editing in the Excel IDE (if you can call it that), and not an outside VBA editor. My current version of Excel is 2010.

I have also scoured Stack Overflow, MSDN, and other websites for an answer but I cannot seem to find one. If I have missed an answer to this question I will remove it if I find one or if someone points it out.

Below is some example code that could be used in the sheet, except the first line always returns an Error. The example code from the MSDN website does not seem to work either.

If Range("A1:B20").Find.Execute(ComboBox1.Value) = True Then

    Range("A1:B20").Find(ComboBox1.Value).Activate

    'And do this

Else

    MsgBox ("No data available for this selection.")

    'And do this
End If

I noticed that the Find object and Execute method you are using appear to be Microsoft Word objects and methods, not Excel. — Soulfire
@Soulfire Thank you, that seemed to be the case. I was thinking it returned a Boolean, which it does in word, but it returns a Range Object in Excel. — Joe S

Michael S Priz Michael S Priz · Accepted Answer · 2015-08-24T12:39:29

First off, if you are going to continue to work in VBA I highly recommend bookmarking this doc page. It will be invaluable to you going forward :)

When you look at the documentation for Range.Find it says there is one required parameter. In your first if statement you try to call it with no parameters:

If Range("A1:B20").Find.Execute(ComboBox1.Value) = True Then

Note that the doc also says

This method returns Nothing if no match is found.

Presumably what you want to be doing then is something like:

Dim FoundRange as Range
Set FoundRange = Range("A1:B20").Find(ComboBox1.Value)

if not(FoundRange is Nothing) then
    Range("A1:B20").Find(ComboBox1.Value).Activate
    'And do this
else
    MsgBox ("No data available for this selection.")
end if

Range.Find.Execute error "Argument Not Optional" Excel VBA

3 Answers