determine if cell address is within a range of cells

[SteelDetailer]

It's late and I'm hitting a wall.

I need to determine if the activecell is within a range of cells. I've tried:
if activecell.address = range("A16:C40") then....
but I get a type mismatch. I'm can't find what I'm looking for in the VBA
help, but I'm probably not asking the right question.

Will someone help a novice, please???

TIA,
Rich

[Carlos]

Try

dim rng_cells as Range
set rng_cells= range("A16:C40")
set int=Intersect(ActiveCell,rng_cells) '

if not int is nothing then ...



"SteelDetailer" <[email protected]> wrote in message
news:[email protected]...
> It's late and I'm hitting a wall.
>
> I need to determine if the activecell is within a range of cells. I've
> tried:
> if activecell.address = range("A16:C40") then....
> but I get a type mismatch. I'm can't find what I'm looking for in the VBA
> help, but I'm probably not asking the right question.
>
> Will someone help a novice, please???
>
> TIA,
> Rich

[SteelDetailer]

I don't think this is quite what I'm lookinig for....

I want to know if cell B12 is within a set of cells that are defned by A16,
C16, A40 and C40. Preferably returni either Trus or False.

Thanks!!
Rich

"Carlos" wrote:

> Try
>
> dim rng_cells as Range
> set rng_cells= range("A16:C40")
> set int=Intersect(ActiveCell,rng_cells) '
>
> if not int is nothing then ...
>
>
>
> "SteelDetailer" <[email protected]> wrote in message
> news:[email protected]...
> > It's late and I'm hitting a wall.
> >
> > I need to determine if the activecell is within a range of cells. I've
> > tried:
> > if activecell.address = range("A16:C40") then....
> > but I get a type mismatch. I'm can't find what I'm looking for in the VBA
> > help, but I'm probably not asking the right question.
> >
> > Will someone help a novice, please???
> >
> > TIA,
> > Rich
>
>
>

[SteelDetailer]

Let me re-phrase. It was late last night and I did explain myself well.
Sorry for the confusion.

I want to find out if the active cell (for example, "B12") is within the
group of cells from A16 (top left corner) to C40 (lower right corner). If
true, I want to do move the cell "focus" to the right one cell (for example,
move focus to "C12").
If the active cell is within D16-D40, I want to move it to the next row down
and to the "A" column.

I will then want to do the same thing for rows E-H and I-L in the same manner.

I have an order form with 3 "column groups". A16, B16, C16 and D16 are all
part of an item description. Likewise for A17, B17, C17 and D17 and the rest
of the rows thru 40 and again for the other "column groups", E-H and I-L.

The following code is what I started with based on Carlos's suggestion:

If Intersect(ActiveCell, Range("A16:C40")) Then Target.Offset(0, 1).Select
If Intersect(ActiveCell, Range("D16:D40")) Then Target.Offset(1, -3).Select

However, if the active cell is C16, the Target.Offset(0,1) is not happening.

Thanks!!
Rich

"SteelDetailer" wrote:

> It's late and I'm hitting a wall.
>
> I need to determine if the activecell is within a range of cells. I've tried:
> if activecell.address = range("A16:C40") then....
> but I get a type mismatch. I'm can't find what I'm looking for in the VBA
> help, but I'm probably not asking the right question.
>
> Will someone help a novice, please???
>
> TIA,
> Rich

[Peter Rooney]

Rich,

A bit late, but does THIS work?

Sub InSearchRange()
Dim Isect1 As Range
Dim Isect2 As Range

Set Isect1 = Intersect(ActiveCell, Range("SearchRange1"))

If Isect1 Is Nothing Then
Else
Selection.Offset(0, 1).Select
End If

Set Isect2 = Intersect(ActiveCell, Range("SearchRange2"))

If Isect2 Is Nothing Then
Else
Range("A" & Selection.Row + 1).Select
End If

End Sub


Regards

Pete


"SteelDetailer" wrote:

> Let me re-phrase. It was late last night and I did explain myself well.
> Sorry for the confusion.
>
> I want to find out if the active cell (for example, "B12") is within the
> group of cells from A16 (top left corner) to C40 (lower right corner). If
> true, I want to do move the cell "focus" to the right one cell (for example,
> move focus to "C12").
> If the active cell is within D16-D40, I want to move it to the next row down
> and to the "A" column.
>
> I will then want to do the same thing for rows E-H and I-L in the same manner.
>
> I have an order form with 3 "column groups". A16, B16, C16 and D16 are all
> part of an item description. Likewise for A17, B17, C17 and D17 and the rest
> of the rows thru 40 and again for the other "column groups", E-H and I-L.
>
> The following code is what I started with based on Carlos's suggestion:
>
> If Intersect(ActiveCell, Range("A16:C40")) Then Target.Offset(0, 1).Select
> If Intersect(ActiveCell, Range("D16:D40")) Then Target.Offset(1, -3).Select
>
> However, if the active cell is C16, the Target.Offset(0,1) is not happening.
>
> Thanks!!
> Rich
>
> "SteelDetailer" wrote:
>
> > It's late and I'm hitting a wall.
> >
> > I need to determine if the activecell is within a range of cells. I've tried:
> > if activecell.address = range("A16:C40") then....
> > but I get a type mismatch. I'm can't find what I'm looking for in the VBA
> > help, but I'm probably not asking the right question.
> >
> > Will someone help a novice, please???
> >
> > TIA,
> > Rich