ERROR 91 with FIND in macro

[Guest]

I can't seem to get around a ERROR 91 message when I make a macro loop with a
FIND function.....any ideas on how to correct this sort of error would be
welcome. I know the data is present and I know it has to be just as it looks
rounded values will not work, I also know it can be part of the cell content.
I'm looking over a full sheet.

A good way to test this would be to place some number of RAND numbers within
a sheet and use the MIN function to find the least......use the FIND function
within a macro to find the least number and select and remove that so it
looks for the next higher value....repeat

Before responding please test your idea….I’ve been given over 100 bad ideas
to date, (with in 1 week)

As far as I can tell the FIND is looking for part of a string and I
understand the 91 ERROR to be some sort of SET value error.

The macro should look something like this with the data in sheet 1 and the
MIN function in sheet 2

Starting in sheet 2 active cell and ending in same.......recorded in relative

ActiveCell.Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
ActiveCell.Cells.Select
Selection.Find(What:="0.110937", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Sheet2").Select
ActiveCell.Offset(-1, 0).Range("A1").Select

 

[Claud Balls]

What are you trying to achieve with these two lines?:

...
ActiveCell.Offset(1, 0).Range("A1").Select
...
...
ActiveCell.Cells.Select
...

 

[Norman Jones]

Hi Robert,

It is rarely necessary to make physical selections, so, with selections
removed, try something like:

Sub Tester03()
Dim FoundCell As Range
Dim sStr As String
sStr = "0.110937" '"Apples" '

With Sheets("Sheet1").Cells

Set FoundCell = .Find(What:=sStr, _
After:=.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not FoundCell Is Nothing Then
FoundCell.ClearContents
End If

End Sub

 

[Guest]

ActiveCell.Offset(1, 0).Range("A1").Select
gives me a copy of the data to be worked

ActiveCell.Cells.Select
okay this may be redondent in some form I agree because it will in al odds
find the cell anyway, But at the page holds nothing but data it also can
cause no real harm

 

[Guest]

Still having problems with this
How do I set the string to the value found with the MIN function in sheet 2
as I explaned before the macro?

Consider this....toss some 30 RAND numbvers any place in sheet 1
let a MIN function in sheet 2 find the least value in sheet 1
Move to that min value in sheet 1 and clear it so the MIN function in shhet
2 moves to the next higher value

Find and deleate all values in order in sheet 1......

But still this is only my way of understanding how to get the FIND to work
with changing values and has little to do with what I'm really doing, what
I;m doing would fill pages.

I have also found I need to select the found cell so that I can look and use
the data around the selected cell back in page 2

The FIND is only a very small part of all the other macros all in all I have
125 meg data in page one and 87meg of macros.....the FIND is the only one I
can't get to work and I can't afford the time to baby sit it with input box's

The value "0.110937" was only the value at the time of recording the macro
but that value changes all the time.....setting the string to that alone will
not work.....how do I set the value found with the MIN function in sheet 2 to
the string value for the find to work in sheet 1?

 

[Tom Ogilvy]

Sub Tester03()
Dim FoundCell As Range
Dim rng as Range

Set rng = Worksheets("Sheet2").Range("B2")
With Sheets("Sheet1")

Set FoundCell = .Cells.Find(What:=rng.value, _
After:=.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


If Not FoundCell Is Nothing Then
.Activate
FoundCell.Select
' FoundCell.ClearContents
End If
End With
End Sub