Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - Return to starting cell | Excel Worksheet Functions | |||
Returning Macro to Starting Cell | Excel Worksheet Functions | |||
Returning Macro to Starting Cell | Excel Worksheet Functions | |||
Getting macro to copy to starting cell | Excel Worksheet Functions | |||
Starting a macro by changing a cell in a column. | Excel Programming |