Hi All,

I am using Excels inputbox in VBA to obtain a cell
reference. See the following

Dim Rng as range
set rng = application.inputbox(Prompt:=prompt...Type:=8)
rng.select

Say rng was C8

What I want to do is to select C8 on each sheet using a
for each statement.

When I try it always slects C8 in sheet1 where the user
originally selected the rng. How can I generalise this so
it selects rng on the activesheet.

Thanks for your help.

Regards

Sadik

Dim Rng as range
Dim sh as Worksheet
On error resume Next
set rng = application.inputbox(Prompt:=prompt...Type:=8)
On Error goto 0
If not rng is Nothing then
for each sh in Worksheets
sh.Activate
range(rng.address).Select
Next
End if


--
Regards,
Tom Ogilvy

"sadik" wrote in message
...
Hi All,

I am using Excels inputbox in VBA to obtain a cell
reference. See the following

Dim Rng as range
set rng = application.inputbox(Prompt:=prompt...Type:=8)
rng.select

Say rng was C8

What I want to do is to select C8 on each sheet using a
for each statement.

When I try it always slects C8 in sheet1 where the user
originally selected the rng. How can I generalise this so
it selects rng on the activesheet.

Thanks for your help.

Regards

Sadik

One way:

To select "rng on the activesheet":

Dim rng As Range
On Error Resume Next 'in case user clicks Cancel
Set rng = Application.InputBox(Prompt:="prompt:", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then _
ActiveSheet.Range(rng.Address).Select

Not sure how that squares with your wanting "to select C8 on each sheet
using a for each statement", which would be more like:

Dim wkSht As Worksheet
Dim rng As Range
On Error Resume Next 'in case user clicks Cancel
Set rng = Application.InputBox(Prompt:="prompt:", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
For Each wkSht In Worksheets
wkSht.Range(rng.Address).Select
'do something, presumably
Next wkSht
End If



In article ,
"sadik" wrote:

Hi All,

I am using Excels inputbox in VBA to obtain a cell
reference. See the following

Dim Rng as range
set rng = application.inputbox(Prompt:=prompt...Type:=8)
rng.select

Say rng was C8

What I want to do is to select C8 on each sheet using a
for each statement.

When I try it always slects C8 in sheet1 where the user
originally selected the rng. How can I generalise this so
it selects rng on the activesheet.

Thanks for your help.

Regards

Sadik

Oops, forgot to edit: in the second function, substitute

With wkSht.Range(rng.Address)
'do something, presumably
End With

For

wkSht.Range(rng.Address).Select



In article ,
JE McGimpsey wrote:

One way:

To select "rng on the activesheet":

Dim rng As Range
On Error Resume Next 'in case user clicks Cancel
Set rng = Application.InputBox(Prompt:="prompt:", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then _
ActiveSheet.Range(rng.Address).Select

Not sure how that squares with your wanting "to select C8 on each sheet
using a for each statement", which would be more like:

Dim wkSht As Worksheet
Dim rng As Range
On Error Resume Next 'in case user clicks Cancel
Set rng = Application.InputBox(Prompt:="prompt:", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
For Each wkSht In Worksheets
wkSht.Range(rng.Address).Select
'do something, presumably
Next wkSht
End If