Verify starting cell for macro

[Don Cameron]

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

 

[Harald Staff]

Hi Don

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

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

 

[Don Cameron]

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

 

[Tom Ogilvy]

if ActiveCell.Column = 153 then

But it seems like only the row is important

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