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

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

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

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

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

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

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