I need to check the same range.value in each sheet in my workbook.
Dim Sh As Object
Sh=Worksheet
For Each Sh in Workbook
But VBA asks for an Object, apparently not explicit enough. What code do
I need to check the same range.value on each worksheet??
TY
Lloyd


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dim sh as Worksheet
Dim rng as Range
for each sh in thisWorkbook.worksheets
set rng = sh.range("A1:B10")
for each cell in rng

Next
Next

--
Regards,
Tom Ogilvy


Lloyd <jghflkd!!@dghrtyd_tt wrote in message
...
I need to check the same range.value in each sheet in my workbook.
Dim Sh As Object
Sh=Worksheet
For Each Sh in Workbook
But VBA asks for an Object, apparently not explicit enough. What code do
I need to check the same range.value on each worksheet??
TY
Lloyd


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for your reply, I hadnt thought of nesting a For Each structure,
accessing each sheet and then respective range in turn.
Question,, can I declare a range using this same structure which will
include each range on each sheet??
Such as

Range(MyRange)=Sh(a).Range(M1),Sh(b).Range(M1),Sh( c).Range(M1).....
Kind of like a 3D range??
Will VBA do that?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

No. Range objects have a parent - so a range can't refer to but one sheet.

Depending on what you are going to do you might be able to do this

Worksheets.Select
Range("M1").Select
Selection.Value = 21
worksheets(1).Select



or for a subset of sheets

Worksheets(Array("sheet1", "sheet3", "sheet5")).Select
Range("M1").Select
Selection.Value = 21
Worksheets(1).Select

Regards,
Tom Ogilvy


Lloyd Peck wrote in message
...

Thanks for your reply, I hadnt thought of nesting a For Each structure,
accessing each sheet and then respective range in turn.
Question,, can I declare a range using this same structure which will
include each range on each sheet??
Such as

Range(MyRange)=Sh(a).Range(M1),Sh(b).Range(M1),Sh( c).Range(M1).....
Kind of like a 3D range??
Will VBA do that?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Lloyd,

Try:

For each sh in Thisworkbook.Sheets
msgbox sh.Range("A1").value
Next sh


No need to Dim the Worksheet.
By the way, its not Sh=Worksheet.


To set a variable equal to a worksheet value I would use:

Dim sh as Worksheet
Set sh=Worksheets("DataValues")
msgbox sh.range("A1").value

HTH,
Alex@JPCS



"Lloyd" <jghflkd!!@dghrtyd_tt wrote in message
...
I need to check the same range.value in each sheet in my workbook.
Dim Sh As Object
Sh=Worksheet
For Each Sh in Workbook
But VBA asks for an Object, apparently not explicit enough. What code do
I need to check the same range.value on each worksheet??
TY
Lloyd


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!