Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel macro
Can anyone please help me with some code for a macro that will achieve the
following in an Excel spreadsheet: 1. Go to 'Clients' worksheet if currently on another worksheet 2. Then go to a cell which is the cross point of Column W and the Row defined by the number in cell A100 Many thanks, V |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel macro
Try
Sub macro() Set ws = ThisWorkbook.Worksheets("Clients") ws.Activate rng = "W" & ws.Range("A100") ws.Range(rng).Select End Sub "Victor Delta" wrote: Can anyone please help me with some code for a macro that will achieve the following in an Excel spreadsheet: 1. Go to 'Clients' worksheet if currently on another worksheet 2. Then go to a cell which is the cross point of Column W and the Row defined by the number in cell A100 Many thanks, V |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel macro
"Sheeloo" wrote in message
... Try Sub macro() Set ws = ThisWorkbook.Worksheets("Clients") ws.Activate rng = "W" & ws.Range("A100") ws.Range(rng).Select End Sub Many thanks, V |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel macro
"Victor Delta" wrote in message
... "Sheeloo" wrote in message ... Try Sub macro() Set ws = ThisWorkbook.Worksheets("Clients") ws.Activate rng = "W" & ws.Range("A100") ws.Range(rng).Select End Sub This worked fine until I inserted some additional rows into the spreadsheet the other day and therefore needed the range formula reference to change to A105 rather than A100. Is there a way to improve the formula so this happens automatically in future? TIA V |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel macro
Select the cell that contains that important information.
Then give it a nice name by typing something like this in the name box (to the left of the formula bar): myRowNumber (remember to hit enter when finish your typing!) Then change the code to something like: rng = "W" & ws.Range(myRowNumber).Value (I like to qualify the property!) Victor Delta wrote: "Victor Delta" wrote in message ... "Sheeloo" wrote in message ... Try Sub macro() Set ws = ThisWorkbook.Worksheets("Clients") ws.Activate rng = "W" & ws.Range("A100") ws.Range(rng).Select End Sub This worked fine until I inserted some additional rows into the spreadsheet the other day and therefore needed the range formula reference to change to A105 rather than A100. Is there a way to improve the formula so this happens automatically in future? TIA V -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel macro
"Dave Peterson" wrote in message
... Select the cell that contains that important information. Then give it a nice name by typing something like this in the name box (to the left of the formula bar): myRowNumber (remember to hit enter when finish your typing!) Then change the code to something like: rng = "W" & ws.Range(myRowNumber).Value (I like to qualify the property!) Brilliant! Many thanks, V |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with an Excel macro
"Victor Delta" wrote in message
... "Dave Peterson" wrote in message ... Select the cell that contains that important information. Then give it a nice name by typing something like this in the name box (to the left of the formula bar): myRowNumber (remember to hit enter when finish your typing!) Then change the code to something like: rng = "W" & ws.Range(myRowNumber).Value (I like to qualify the property!) Brilliant! Many thanks, V Actually, didn't work until I discovered by trial and error that the inverted commas had to be put back! Final code therefo rng = "W" & ws.Range("myRowNumber").Value V |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
passing arguments from an excel macro to a word macro | Excel Discussion (Misc queries) |