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.

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

wrote:

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

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

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
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,

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


Dave Peterson wrote:
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

wrote:

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

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

wrote in message
oups.com...
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


Dave Peterson wrote:
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

wrote:

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

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

Tom Ogilvy wrote:
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

wrote in message
oups.com...
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


Dave Peterson wrote:
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

wrote:

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