Verify starting cell for macro

  • Thread starter Thread starter Don Cameron
  • Start date Start date
D

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
 
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
 
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
 
if ActiveCell.Column = 153 then

But it seems like only the row is important

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