FIND function trouble

[Laurence Lombard]

The code below sorts a range to the current column and finds the last
occurrence of the value in Activecell. This works well except if the current
column has formulas and values mixed - then Find returns "Nothing"
(displayed by my message). It seems to be inconsistent though (does not
always return Nothing). Can anyone explain what is going on?
Thanks
Laurence

Sub SortToCurrentColumn_Click()
'Sort_to_CurrentColumn()
Sheets("Cash Bks").Activate
CurrentColumn = ActiveCell.Column
CurrentCellValue = ActiveCell.Value
Lastrow = Range("B10000").End(xlUp).Row

Range("A2", "O" & Lastrow).Sort _
Key1:=Cells(3, CurrentColumn), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom


Set Foundcell = Range(Cells(1, CurrentColumn), Cells(Lastrow,
CurrentColumn)).Find(What:=CurrentCellValue, _
LookIn:=xlValues, LookAt:=xlWhole, searchdirection:=xlPrevious)


If Foundcell Is Nothing Then
MsgBox "Nothing"
Else
Foundcell.Activate
End If

End Sub

 

[Bob Phillips]

Laurence,

I don't get the problem you describe, I can find the data okay. What I do
get is that the formulae get messed up when the data is sorted. Why are you
sorting, it is not necessary for the Find? If the sort is removed, does the
code work okay for you?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Laurence Lombard]

Thank you for your prompt reply.

I will Cc this directly to you in addition to the newsgroup as you might not
pick up that I have replied - there are so many posts arriving at the group.


I have found the problem, but still not sure how to work around it. First
your query.

The database is a Cashbook with
Date,Description,Reference,Amount,Tax,Nett,Code etc. I often want to group
things together to look for similar entries (Date, Description, Code etc)
and then go to the last entry of that group with the Find. It works really
well.
In my case the formulas don't get messed up (they reference cells in the
same row, so the sort should not affect them)

Some columns are formatted to two decimals (eg 5000.00) but
Activecell.Value returns "5000" so the Find function looks for "5000" .
Likewise the date column is formatted "29/05/03" but Activecell.Value
returns "29/05/2003" and consequently the Find returns a Nothing. I need to
do a Find for the Activecell in its current format. Maybe you can help.

Thanks once again.
Laurence

 

[Laurence Lombard]

I found the solution - must use Activecell.Text this stores the value as it
is displayed.

Excel is amazing!
Laurence