Find syntax

Bradium

Board Regular
Joined
Oct 8, 2008
Messages
129
Hi guys

---a ---b---c---d---e---f
1 100 200 300 400 500 600

This works

Sub testmyrange()
mystart = Range("a1:f1").Find(100).Column
End Sub

This does not

Sub testmyrange()
mystart = Range("a1:f1").Find(500).Column
End Sub


I would be most appreciative if someone could enlighten me as to what I am doing wrong.

Regards

Brad
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Is the correct worksheet active when you run the second piece of code?

The procedure will find the value in A1:F1 of whatever is the currently selected sheet.

Code:
Sub testmyrange()
mystart = Worksheets("Sheet1").Range("a1:f1").Find(500).Column
End Sub
 

Bradium

Board Regular
Joined
Oct 8, 2008
Messages
129
Thanks for your reply. It is the sheet I am working on. It would find 100 200 and 300 but not 400 or 500.



I have spent quite some time on this and can guess from http://www.thecodenet.com/articles.php?id=18

To avoid problems, set these arguments explicitly each time you use this method!

Not really sure what they meant by this


So I just saved my file and shut it down. Then I opened it and it still failed.
Then I shut down excel and reopened the file and it worked.

Maybe excel was just playing up. Got to go home now so I look forward to any comments on the volatility of this issue when I check in tomorrow.

Regards

Brad
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
To explicitly set the arguments you need to not only identify what you're looking for, but where to start looking, whether to look at values or formulas and various other arguments.

can be written as:
Code:
Sub testmyrange()
    mystart = Range("a1:f1").Find( _
        What:=500, _
        After:=Range("A1"), _
        LookIn:=xlValues, _
        LookAt:=xlWhole _
        ).Column
End Sub

or to search Sheet1:
Code:
Sub testmyrange()
    With Worksheets("Sheet1")
        mystart = .Range("a1:f1").Find( _
            What:=500, _
            After:=.Range("A1"), _
            LookIn:=xlValues, _
            LookAt:=xlWhole _
            ).Column
    End With
End Sub

or you can do it without naming the arguments, but the arguments must be in the correct order.
Code:
Sub testmyrange()
        mystart = Range("a1:f1").Find(500, Range("A1"), , xlWhole).Column
End Sub
Note here I've left out the LookIn:=xlValues argument but it still needs the commas to indicate where it should go. In this case it will use the value set in a previous search.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,272
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I have spent quite some time on this and can guess from http://www.thecodenet.com/articles.php?id=18

To avoid problems, set these arguments explicitly each time you use this method!

Not really sure what they meant by this
The syntax of the Find method is
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
That is, Find has 9 arguments. You have only set one of those arguments in your code and the suggestion is to set all of them each time. If the previous search was looking to 'Match entire cell contents' and you do not specify this argument (LookAt) then you search will also try to 'Match entire cell contents'. This may not be what you want, so it is a good idea to specifically set the arguments.

Look in the built-in vba help to see more information about the arguments and about Find generally.

(I'm not sure why you originally got the behaviour you described. I was not able to reproduce it)
 

Bradium

Board Regular
Joined
Oct 8, 2008
Messages
129
Thanks for the enlightenment Delmar D'Percy and Peter_SSs.

After firing up excel this morning, the example worked and so did your code. After running a different "find" code I had the problem again. So close excel and it all works.

The message I get is: run time error 91
Object variable or with block variable not set.

I will give your ideas a go and see what I come up with. Setting all the aguments may be the problem. I will let you know how I get on when I get to the bottom of it.

Much appreciated

Brad
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,272
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

After running a different "find" code I had the problem again.
Perhaps if we could see both sets of code and knew what the sheet(s) they were operating on had in them in terms of layout and data, some reason might become apparent.
 

Bradium

Board Regular
Joined
Oct 8, 2008
Messages
129
Hi Guys

I have uploaded the file I have been playing with, for you to have a look at if you are curious.

My code is working well now, but I never really got to the bottom of the problem. I'm sure you are both right with the set
.
http://www.4shared.com/document/Z2fTIjwt/Find_Issues.html

The "failed macro" button will cause the good macro on the "Show Gantt" button to fail. I have only been able to reset by shutting down excel. I do not need to run both. I'm just interested to understand the process.

My recorded macro "Macro1" does not reset the find settings.

Thanks again for all your help

Brad xl2003 at work and xl2000 at home.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,272
Office Version
  1. 365
Platform
  1. Windows
Not logical, I know, but try changing
Rich (BB code):
LookIn:=xlValues
to
Rich (BB code):
LookIn:=xlFormulas
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,613
Messages
5,832,702
Members
430,155
Latest member
spacedad41

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