Solved: Intersect

[Opv]

I have defined sRng as a range and set the range to a designated range of cells. However, the following receives an error:

[VBA]
If Not Intersect(ActiveCell, Range(sRng)) Is Nothing Then
[/VBA]

I have tested sRng and have confirmed that it is a valid range. I can select it and change values within that defined range. Is there something within Intersect that prefers ranges like "A3:A500" as opposed to a named range comprised of those same cells?

[Simon Lloyd]

replace your code with this:[vba]Dim sRng As Range
Set sRng = Range("B210")
If Not Intersect(ActiveCell, sRng) Is Nothing Then
MsgBox ActiveCell.Address
End If[/vba]you should find that when clicking a cell in the range B210 you will get the cell address, this is because sRng is already defined as a range so no need for Range(sRng), did you define sRng as i did above? or is it a worksheet range?

If its a worksheet defined range then you need this:[vba]If Not Intersect(ActiveCell, Range("sRng")) Is Nothing Then
MsgBox ActiveCell.Address
End If
[/vba]

[Opv]

Thanks.

[Opv]

Why would I be getting a type mismatch error with the following?

[VBA]
If Not Intersect(ActiveCell, Range("Age").Column) Is Nothing Or _
Not Intersect(ActiveCell, Range("KidDueDate").Column) Is Nothing Or _
Not Intersect(ActiveCell, Range("NextWormDate").Column) Or _
Not Intersect(ActiveCell, Range("Status").Column) Then

ActiveCell.Offset(0, -1).Select
End If

[/VBA]

[Simon Lloyd]

Why are you using .Column?, you don't need it as the intersect is referring to a range with reference to the activecell.

[Opv]

Why are you using .Column?, you don't need it as the intersect is referring to a range with reference to the activecell.

I see that now. I guess I was braindead at the time. I was originally trying to test for whether the ActiveCell is in either of the four columns. I see now that what I wrote was way off base.

Thanks,

Opv

[mdmackillop]

I was originally trying to test for whether the ActiveCell is in either of the four columns

To do that, use .EntireColumn (assuming that is not defined already by the range name)

[VBA]
Intersect(ActiveCell, Range("Age").EntireColumn)
[/VBA]

[Opv]

To do that, use .EntireColumn (assuming that is not defined already by the range name)

[vba]
Intersect(ActiveCell, Range("Age").EntireColumn)
[/vba]

Thanks, as always.

Opv

[Opv]

Well, I'm having another Intersect problem with the following script.

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim toSale As Range

Set toSale = Range("ToSale").Offset(1, 0).Resize(gRows - hRow, 1).SpecialCells(xlCellTypeVisible)

If Not Intersect(Target, toSale) Is Nothing Then
' If Range("ToSale").Value = "Select" Then
' Target.Value = "X"
' End If

MsgBox Target.address
End If[/vba]


I understood that Intersect(Target, Range) tested whether the Target cell (or activecell) was within the designated range. The above script is somehow resulting in Target.address being the same as the toSale range. How could the above be changed so that individual cells within the toSale range are changed to "X" rather than the entire range being changed?

[Simon Lloyd]

You cannot keep giving us snippets of changed code and expect us to understand whats going on in your head!

You have now shown two other variables gRows and hRow yet they aren't declared or defined, it's about time you supplied a workbook so we can see what you are trying to acheive!

[Opv]

You cannot keep giving us snippets of changed code and expect us to understand whats going on in your head!

You have now shown two other variables gRows and hRow yet they aren't declared or defined, it's about time you supplied a workbook so we can see what you are trying to acheive!

Sorry about that. This question has nothing to do with my earlier question, except to the extent that it also involves the Intersect statement. I withdraw the question.

Thanks