Try changing the search conditions to match the entire cell contents eg:

'------------------------------------------------
Dim rng1 As Range
Dim hpc As Double
Dim hpcStr As String
Dim fndCell As Range
Set rng1 = _
Application.Union(Range("E" & sr & ":" & "E" & num) _
, Range("G" & sr & ":" & "G" & num))
rng1.FormulaR1C1 = "=(RC[-1]-RC[-3])*100/(RC[-3])"
rng1.NumberFormat = "#,##0.0000"

hpc = Application.WorksheetFunction.Max(rng1)
hpcStr = Format(hpc, "#,##0.0000")

With rng1
Set fndCell = .Find(What:=hpcStr, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
End With
'-------------------------------------------------


LookAt:=xlPart changed to LookAt:=xlWhole

Hope this helps
Rowan


jesmin wrote:
Hi Rowan:

Ref. to my last prob that you solved. Today I am having a strange
prob. I am calculating min and max values in some excel col range s and
finding them and locating the cells. The values are being too big
double, I was formatting them as: "0.0000" and "0.0"(ex:1.2034,-1.2,0.9
etc).
Prob: I have 2 values min -5.4 and max 5.4. When using FIND() for both
values it refering to the same cell(say cells(20,2) which one comes
first.(search by column).
So the max & min works fine. But for -5.4 and 5.4 why its refering to
same cell?
fndcell = .Find(What:=hpcStr, .....your code below.

Note: The formatted data are 1.2345,-1.3340(I used "0.0000"--Am I
right, how about +-sign) and 1.3,1.0,-1.1,-0.9(I used "0.0" Am I OK).

Thanks again