Help with an Excel macro

[Victor Delta]

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

 

[Sheeloo]

Try
Sub macro()
Set ws = ThisWorkbook.Worksheets("Clients")
ws.Activate
rng = "W" & ws.Range("A100")
ws.Range(rng).Select
End Sub

 

[Victor Delta]

Try
Sub macro()
Set ws = ThisWorkbook.Worksheets("Clients")
ws.Activate
rng = "W" & ws.Range("A100")
ws.Range(rng).Select
End Sub

Many thanks,

V

 

[Victor Delta]

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]

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]

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

 

[Victor Delta]

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 therefore: rng = "W" & ws.Range("myRowNumber").Value

V