I am having a problem with the Find method of Range.

I have a macro that searches a worksheet using the Find method for a
particular value in column 1. Normally it will find the value and
return the range. However, sometimes when I rerun the macro and its
search for the same value it won't find it and returns nothing.

I have traced the execution and can see the value that it is search for
and I am certain that value exists in the range of the first column.
I'm using Find and not FindNext so I'm assuming every call to Find
resets to the top of the column and search down until found or not.

I have cut out three of the lines from the macro to show the exact
syntax I am using. Both ranges rJoints and rElements contain formulas
so I'm looking for values.

Set rJoints = wsPlotsheet.Columns(53) ' Column BA
Set rElements = Worksheets("Elements").Range("A1")
Set rFind = rJoints.Find(What:=rElements.Offset(i, 2), LookIn:=xlValues,
LookAt:=xlWhole)

To illustrate what the macro does is, it generates graphics with text
associates with each shape. I run the macro and it creates a new
worksheet with the graphics and associated text representing an
attribute. It works. I simply rerun the macro requesting the same
graphics but with a different attribute. The graphics should be
identical only the attribute changes. But it doesn't work, and I've
trace it to the Find method. It found the values the first time I run
but doesn't find the same value the next time.

Any suggestions would be appreciated.


*** Sent via Developersdex http://www.developersdex.com ***

Excel's Find (edit|Find) and VBA's find share the same settings.

If you do Edit|find manually and change one of the options (matchcase???), then
if you don't tell your code what to use, it'll use what was last used.

I'd specify every parm for that .find in your code.



Edward Ulle wrote:

I am having a problem with the Find method of Range.

I have a macro that searches a worksheet using the Find method for a
particular value in column 1. Normally it will find the value and
return the range. However, sometimes when I rerun the macro and its
search for the same value it won't find it and returns nothing.

I have traced the execution and can see the value that it is search for
and I am certain that value exists in the range of the first column.
I'm using Find and not FindNext so I'm assuming every call to Find
resets to the top of the column and search down until found or not.

I have cut out three of the lines from the macro to show the exact
syntax I am using. Both ranges rJoints and rElements contain formulas
so I'm looking for values.

Set rJoints = wsPlotsheet.Columns(53) ' Column BA
Set rElements = Worksheets("Elements").Range("A1")
Set rFind = rJoints.Find(What:=rElements.Offset(i, 2), LookIn:=xlValues,
LookAt:=xlWhole)

To illustrate what the macro does is, it generates graphics with text
associates with each shape. I run the macro and it creates a new
worksheet with the graphics and associated text representing an
attribute. It works. I simply rerun the macro requesting the same
graphics but with a different attribute. The graphics should be
identical only the attribute changes. But it doesn't work, and I've
trace it to the Find method. It found the values the first time I run
but doesn't find the same value the next time.

Any suggestions would be appreciated.

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

In this particular case the search is for simply integer numbers. Also
I don't do anything in Excel simply rerun the macro. I have a dialog
box that identifies the different attributes to display with the shapes.

So, I run the macro, identify the attribute. When the plot is
completed, I immediately rerun the macro and select a different
attribute.

By the way I am using Excel 2000 SR-1 with W2000 SP-4.




*** Sent via Developersdex http://www.developersdex.com ***

I don't have another suggestion--but I'd still follow the first one.

You may want to boil your code down to just the relevant portion and post
it--and a bit of the data that should work, but won't.

Well, one more question: You don't have any merged cells in that range do you?

Edward Ulle wrote:

In this particular case the search is for simply integer numbers. Also
I don't do anything in Excel simply rerun the macro. I have a dialog
box that identifies the different attributes to display with the shapes.

So, I run the macro, identify the attribute. When the plot is
completed, I immediately rerun the macro and select a different
attribute.

By the way I am using Excel 2000 SR-1 with W2000 SP-4.

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

"Edward Ulle" wrote in message
...


I'm using Find and not FindNext so I'm assuming every call to Find
resets to the top of the column and search down until found or not.

Not necesarilly. Where to start from and which directionto look are
parameters to find. Like this:

Find(What:="abcdef", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

As Dave said, set everything yourself.