Verify starting cell for macro
 

I have written a macro which extracts data from cells in the same row and
sets it out on a new sheet created in the same workbook. The macro
dimensions variables (approx 100 of them) which are given values by using
the expression "NameofVariable=ActiveCell.Offset(0,-x) where x is the number
of colums before the cell selected by cursor before running the macro.
In the present case the required starting cell must be within the range
column EW row 6 (+) where the rows go on increasing ( initially 20 and
increasing by about 6 per month).
My difficulty is (and hence the present question) How do I write an error
trap at the beginning of the macro to check that the starting cell selected
falls within the required range? If it doesn't then message "Do you mean
xxxx " where xxxx is the contents of col EW in the same row.

Have been running in circles on this and would now appreciate being pointed
in the right direction.

Thanks in anticipation

Don Cameron

Verify starting cell for macro
 

Hi Don

I'm not sure I fully understand what you need, but here's something that tests if you are
within the range B3:D14:

Sub test()
If Intersect(ActiveCell, Range("B3:D14")) Is Nothing Then
MsgBox "Somewhere in B3:D14 please"
Else
MsgBox "Yo da man."
End If
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Don Cameron" wrote in message
...
I have written a macro which extracts data from cells in the same row and
sets it out on a new sheet created in the same workbook. The macro
dimensions variables (approx 100 of them) which are given values by using
the expression "NameofVariable=ActiveCell.Offset(0,-x) where x is the number
of colums before the cell selected by cursor before running the macro.
In the present case the required starting cell must be within the range
column EW row 6 (+) where the rows go on increasing ( initially 20 and
increasing by about 6 per month).
My difficulty is (and hence the present question) How do I write an error
trap at the beginning of the macro to check that the starting cell selected
falls within the required range? If it doesn't then message "Do you mean
xxxx " where xxxx is the contents of col EW in the same row.

Have been running in circles on this and would now appreciate being pointed
in the right direction.

Thanks in anticipation

Don Cameron

Verify starting cell for macro
 

Harald - Thank you for your very prompt reply.
I have experimented and this trap will do what I want.
My range will be ("EW6:EW20") although it will increase (ie rows will be
added) so perhaps I will just make it ("EW6:EW200) to allow for future
expansion.

Thanks again
Don


"Harald Staff" wrote in message
...
Hi Don

I'm not sure I fully understand what you need, but here's something that
tests if you are
within the range B3:D14:

Sub test()
If Intersect(ActiveCell, Range("B3:D14")) Is Nothing Then
MsgBox "Somewhere in B3:D14 please"
Else
MsgBox "Yo da man."
End If
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Don Cameron" wrote in message
...
I have written a macro which extracts data from cells in the same row
and
sets it out on a new sheet created in the same workbook. The macro
dimensions variables (approx 100 of them) which are given values by
using
the expression "NameofVariable=ActiveCell.Offset(0,-x) where x is the
number
of colums before the cell selected by cursor before running the macro.
In the present case the required starting cell must be within the range
column EW row 6 (+) where the rows go on increasing ( initially 20 and
increasing by about 6 per month).
My difficulty is (and hence the present question) How do I write an
error
trap at the beginning of the macro to check that the starting cell
selected
falls within the required range? If it doesn't then message "Do you
mean
xxxx " where xxxx is the contents of col EW in the same row.

Have been running in circles on this and would now appreciate being
pointed
in the right direction.

Thanks in anticipation

Don Cameron

Verify starting cell for macro
 

if ActiveCell.Column = 153 then

But it seems like only the row is important

set rng = Cells(ActiveCell.Row,153)
rng.Activate

--
Regards,
Tom Ogilvy



Don Cameron wrote in message
...
Harald - Thank you for your very prompt reply.
I have experimented and this trap will do what I want.
My range will be ("EW6:EW20") although it will increase (ie rows will be
added) so perhaps I will just make it ("EW6:EW200) to allow for future
expansion.

Thanks again
Don


"Harald Staff" wrote in message
...
Hi Don

I'm not sure I fully understand what you need, but here's something that
tests if you are
within the range B3:D14:

Sub test()
If Intersect(ActiveCell, Range("B3:D14")) Is Nothing Then
MsgBox "Somewhere in B3:D14 please"
Else
MsgBox "Yo da man."
End If
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Don Cameron" wrote in message
...
I have written a macro which extracts data from cells in the same row
and
sets it out on a new sheet created in the same workbook. The macro
dimensions variables (approx 100 of them) which are given values by
using
the expression "NameofVariable=ActiveCell.Offset(0,-x) where x is the
number
of colums before the cell selected by cursor before running the macro.
In the present case the required starting cell must be within the
range
column EW row 6 (+) where the rows go on increasing ( initially 20 and
increasing by about 6 per month).
My difficulty is (and hence the present question) How do I write an
error
trap at the beginning of the macro to check that the starting cell
selected
falls within the required range? If it doesn't then message "Do you
mean
xxxx " where xxxx is the contents of col EW in the same row.

Have been running in circles on this and would now appreciate being
pointed
in the right direction.

Thanks in anticipation

Don Cameron