Hi Rowan:The code:
Dim rng1, rng2, rngt,lv,hv,yr1,yr2 As Variant
Set rng1 = _
Application.Union(Range("E" & sr & ":" & "E" & num), Range("G" & sr &
":" & "G" & num))
rng1.FormulaR1C1 = "=RC[-3]"

Set rng2 = _
Application.Union(Range("B" & sr & ":" & "B" & num), Range("D" & sr &
":" & "D" & num))
lv = Application.WorksheetFunction.Min(rng2)
yr1 = valcal(lv, rng2)

Set rngt = Application.Union(Range("C" & sr + 1 & ":" & "C" & num),
rng1)
hv = Application.WorksheetFunction.Max(rngt)--It has a value
yr2 = valcal(hv, rngt) ----not working here

------------
Function valcal(val, mrn)
Dim r, c As Integer
Dim yr As String
Dim fndCell As Range
With mrn
Set fndCell = Cells.Find(What:=val, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext _
, MatchCase:=False)
If Not fndCell Is Nothing Then
r = fndCell.Row
c = fndCell.Column
yr = Cells(r, 1).Value
yr = Right("0" & c, 2) & yr
Else
MsgBox "Not Found"
End If
Stop
End With
End Function


--result: In 2nd call, FIND not working


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932