Thanks. No syntex wise code is fine(I was typing actually).
Only the 2nd FIND case its not working. 1st cells.FIND working well.
2nd cells.find(what:=var).activate --this part is not working

Am I doing wrong in writing both cells.FIND similar way(Ommiting other
find parameters).???
l1 = Application.WorksheetFunction.Max(rng2)
Cells.Find(What = l1).Activate------working fine

l2 = Application.WorksheetFunction.Max(rngt)
Cells.Find(What = l2).Activate------Error: Object or With block not set


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

You would be better off cutting and pasting your code directly from the
VBE into your message. There are a number of typos in here which makes
it hard to say what the real problem is eg
Cells.Find(What = l1).Activate
should be
Cells.Find(What:= l1).Activate

Two pointers when using the find method. One it will remember the
settings from the last time it was used either manually or in a macro so
you should specify all relavant parameters.
Secondly you should cater for the fact that you may not find what you
are looking for. So your code would look something like:

Dim fndCell as range
Set fndCell = Cells.Find(What:=l2, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext _
, MatchCase:=False)
If not fndCell is nothing then
fndcell.activate
else
msgbox "Not Found"
End if

Hope this helps
Rowan

jesmin wrote:
Thanks. No syntex wise code is fine(I was typing actually).
Only the 2nd FIND case its not working. 1st cells.FIND working well.
2nd cells.find(what:=var).activate --this part is not working

Am I doing wrong in writing both cells.FIND similar way(Ommiting other
find parameters).???
l1 = Application.WorksheetFunction.Max(rng2)
Cells.Find(What = l1).Activate------working fine

l2 = Application.WorksheetFunction.Max(rngt)
Cells.Find(What = l2).Activate------Error: Object or With block not set

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

Hi Jesmin

Worked fine for me. Are you getting an error message, unexpected
results, etc etc?

Regards
Rowan

jesmin wrote:
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

Hi rown:

In the 2nd call, fndcell is empty. The range selection is perfect. Its
selecting the required columns. Only it canot find the value.

Thanks


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

As it is your function is not passing a value back to the main
procedure. Try changing it to:

Function valcal(val, mrn)
Dim r, c As Integer
Dim yr As String
Dim fndCell As Range
With mrn
Set fndCell = .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
valcal = Right("0" & c, 2) & yr '<<changed
End If
End With
End Function

Hope this helps
Rowan

jesmin wrote:
Hi rown:

In the 2nd call, fndcell is empty. The range selection is perfect. Its
selecting the required columns. Only it canot find the value.

Thanks

Hi Rown:

I found the problem. Its the data type. I declared the variable lv, hv
as variant. In 2nd case, hv=1.60026386 and it failed. I put a smaller
number 1.3098 and it ran fine in 2nd call. So I changed them to double.
Now I get another value for hv as 1.3000687785 and this time it failed
again. I will better format the data to 4 decimal first and then will
calculate. Is not 1.3000687785 is a double? What is the safe data type
that I can declare in case of decimals.

Thanks


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