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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,291
Office Version
365
Platform
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,291
Office Version
365
Platform
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
45,921
Office Version
365
Platform
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
45,921
Office Version
365
Platform
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
45,921
Office Version
365
Platform
Windows
Not logical, I know, but try changing
Rich (BB code):
LookIn:=xlValues
to
Rich (BB code):
LookIn:=xlFormulas
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,819
Messages
5,513,584
Members
408,959
Latest member
BenBez

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top