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?

"Norman Jones" wrote:

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


---
Regards,
Norman



"Robert AS" wrote in message
...
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