intersect method error

[robert.hatcher]

I'm experimenting with the intersect method...

I need to locate two values, assign them to variables and then select
the intersection between the two.

I'm trying the following:

Sub IntersectTest()

Dim colRng, uclRng

Dim isect As Range

colRng = ActiveSheet.Cells.Find("DC_RES")

uclRng = ActiveSheet.Cells.Find("UCL")

Set isect = Application.Intersect(Range(colRng).EntireColumn,
(Range(uclRng).EntireRow))

If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If

End Sub

At the Set isect =... line I get:
Run time error : '1004'
Method 'Range' of 'object' Global Failed

Help! I've rewritten this as many ways as I can, to no avail.

 

[Dave Peterson]

If either of those .finds were unsuccessful, then you'll have trouble.

I'd check for that first.

Sub IntersectTest()

'declare those variables as Ranges.
Dim colRng as Range
dim uclRng as range
Dim isect As Range

'use the Set command to assign a range to that range variable
set colRng = ActiveSheet.Cells.Find("DC_RES")
set uclRng = ActiveSheet.Cells.Find("UCL")

if colrng is nothing _
or uclrng is nothing then
msgbox "at least one find failed!
else
Set isect = Application.Intersect(colRng.EntireColumn, uclRng.EntireRow)
'and there'll always be an intersection with a row and column.
isect.Select
End If

End Sub

 

[Tom Ogilvy]

Sub IntersectTest()

Dim colRng as Range, uclRng as Range

Dim isect As Range

set colRng = ActiveSheet.Cells.Find("DC_RES")

set uclRng = ActiveSheet.Cells.Find("UCL")
if colRng is nothing or uclRng is nothing then
msgbox "Not found"
Exit sub
end if
Set isect = Application.Intersect(colRng.EntireColumn, _
uclRng.EntireRow))

' it would be difficult for a row and column not to intersect


End Sub

 

[robert.hatcher]

Dave,

that works thanks I was getting real frustraited

I still get mixed up trying to make good declarations. Why do you need
to use the Set statement when assing a variabvle that is a Range type?

Thanks Again

 

[Tom Ogilvy]

You use set to assign an object to an object variable.

You use let to assign a value to a normal variable. However, the let can
be omitted and usually is.



--
Regards,
Tom Ogilvy

Dave,

that works thanks I was getting real frustraited

I still get mixed up trying to make good declarations. Why do you need
to use the Set statement when assing a variabvle that is a Range type?

Thanks Again

 

[robert.hatcher]

Ok, I think I have it now. When I say "as Range" Im declaring an
object variable. I was under the misunderstanding that in this case
"range" was just a data type... Embarasing that as much as I have read
about declaring and setting variables I didnt get that.

Thanks Tom!
Robert

You use set to assign an object to an object variable.

You use let to assign a value to a normal variable. However, the let can
be omitted and usually is.