Hi: I am selecting 2 ranges formed by union. 1 is working and other is
NOT.
----Code:
Dim rng1, rng2, rngt As Variant
dim l1,l2 as variant
Set rng1 = _
Application.Union(Range("E:10:E25"), Range("G10:G25"))
rng1.FormulaR1C1 = "=RC[-3]"
Set rngt = Application.Union(Range("C11:c25"), rng1)

Set rng2 = _
Application.Union(Range("B10:B25), Range("D10:D25"))

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


Why the 1st is working but no the 2nd one.

Thanks in advance


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

Could it be this line. E:10:E25 ???


Set rng1 = _
Application.Union(Range("E:10:E25"), Range("G10:G25"))


"jesmin" wrote in
message ...

Hi: I am selecting 2 ranges formed by union. 1 is working and other is
NOT.
----Code:
Dim rng1, rng2, rngt As Variant
dim l1,l2 as variant
Set rng1 = _
Application.Union(Range("E:10:E25"), Range("G10:G25"))
rng1.FormulaR1C1 = "=RC[-3]"
Set rngt = Application.Union(Range("C11:c25"), rng1)

Set rng2 = _
Application.Union(Range("B10:B25), Range("D10:D25"))

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


Why the 1st is working but no the 2nd one.

Thanks in advance


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

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