I have a code that loops through 56 Worksheets and on each sheet loops
through the used cells in Column A. Does anyone have a way to execute
the two-phase looping without activating the Worksheets? Following the
line of least resistance, and at the expense of speed, I have:

For each sh in WorkSheets
sh.activate
For each c in Range("a:a")
If Not IsEmpty(c) Then

<Do something

End if
Next
Next


I reckon that the code could be vastly speeded up if it didn't have to
select each sheet.

TIA.

Myles.


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Myles,

Try:

Public Sub Tester03()
Dim SH As Worksheet
Dim c As Range
Dim CalcMode As Long

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each SH In Worksheets
For Each c In SH.Range("a:a")
If Not IsEmpty(c) Then
'<Do something
End If
Next c
Next SH

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============


---
Regards,
Norman


"Myles" wrote in
message ...

I have a code that loops through 56 Worksheets and on each sheet loops
through the used cells in Column A. Does anyone have a way to execute
the two-phase looping without activating the Worksheets? Following the
line of least resistance, and at the expense of speed, I have:

For each sh in WorkSheets
sh.activate
For each c in Range("a:a")
If Not IsEmpty(c) Then

<Do something

End if
Next
Next


I reckon that the code could be vastly speeded up if it didn't have to
select each sheet.

TIA.

Myles.


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi,

I hope, it would help you out. Instead of using collection, you could
use a simple loop. Code could be used as follows:

For x = 1 To Sheets.Count
If Sheets(x).Range("___") = "Validation" Then

your code here...

End If
Next


--
gajendra_vba
------------------------------------------------------------------------
gajendra_vba's Profile: http://www.excelforum.com/member.php...o&userid=29935
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Norman,

Many hanks. Your code does the trick!

I had tried something similar but yours has exposed my mistake. For the
<do something segment of the code, I had a Call statement which
invoked another subroutine . And since this subroutine didn't have
range references qualified by the sheet reference, SH in this case, I
ran into strife as only the activesheet was worked upon. To get round
the problem, I have now abandoned the Call idea and embedded the
subroutine in the main code with all range references duly tacked. This
makes the end-product code very unwieldy but I suppose that is the price
to pay for enhanced speed.

In any event, if you know of a way I can use the Call idea -without-
-activating -the individual sheets, I would welcome it. I flirted with
declaring SH global-to no avail, apparently because SH is an object not
a variable.


Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Another one that failed ...
Passing the current sheet as an argument, as in:

Sub LoopThruShtsIn2Phases()
Dim sh as Worksheet

For each sh in worksheets
Call DoSomething(sh)
Next

End Sub

Sub DoSomething(obSht) *'returns Object doesn't support this property
or method *error
Dim c as range

For each c in obSht.Range("a:a")
Is Not Isempty(c) Then

<CarryOutSomeActions

End if
Next
End Sub


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Myles,

'=============
Public Sub Tester03()
Dim SH As Worksheet
Dim c As Range
Dim CalcMode As Long

On Error GoTo XIT

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each SH In Worksheets
For Each c In SH.Range("A1:A20") '<<==== CHANGE
If Not IsEmpty(c) Then
Call MyMacro(c) '<<==== CHANGE
End If
Next c
Next SH

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

'=============
Sub MyMacro(rng As Range)
rng.Interior.ColorIndex = 6
End Sub
'<<=============

--
---
Regards,
Norman



"Myles" wrote in
message ...

Another one that failed ...
Passing the current sheet as an argument, as in:

Sub LoopThruShtsIn2Phases()
Dim sh as Worksheet

For each sh in worksheets
Call DoSomething(sh)
Next

End Sub

Sub DoSomething(obSht) *'returns Object doesn't support this property
or method *error
Dim c as range

For each c in obSht.Range("a:a")
Is Not Isempty(c) Then

<CarryOutSomeActions

End if
Next
End Sub


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Norman,

Many Thanks again. Great solution!

I found it a bit perplexing though why passing the cell range (c in
the example) as an argument works hitch-free but passing a sheet object
(sh as in my example) throws up error message.

If there is an underlying reason why this is so it would be interesting
to know.

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Myles,

I found it a bit perplexing though why passing the cell range (c in
the example) as an argument works hitch-free but passing a sheet object
(sh as in my example) throws up error message.

If there is an underlying reason why this is so it would be interesting
to know.

The sheet does not have a ColorIndex property and, in any case, it is the
range that you wish to colour.


---
Regards,
Norman



"Myles" wrote in
message ...

Hi Norman,

Many Thanks again. Great solution!

I found it a bit perplexing though why passing the cell range (c in
the example) as an argument works hitch-free but passing a sheet object
(sh as in my example) throws up error message.

If there is an underlying reason why this is so it would be interesting
to know.

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Norman,
I was musing over what happened when I applied the Call idea to my
project.
Using your last tip-off, the structure of my coding becomes:

Sub MainCode()
Dim sh As Worksheet
Dim c As Range

For each sh in WorkSheets
For each c in sh.Range("a:a")

Call RecalculateRowScore(c)

Next
Next
End sub

Sub RecalculateRowScore(rng as Range)

If Instr(rng, "-")=0 Then
rng.Offset(0,1).value=Len(rng)*Asc(Mid(rng,1,1))
ElseIf Instr(rng, "-")0 Then
rng.Offset(0,1).value="-"
End If

<Other codes follow

End if
End Sub


The foregoing works perfectly (as does your cell colouring example).
However, rather than parse the cell c as argument, it appears
intuitively appealing to plump for parsing the worksheet object sh to
the servient subroutine. This option produces:

Sub MainCode2()
Dim sh As Worksheet
Dim c As Range

For each sh in WorkSheets

Call RecalculateRowScore2(sh)

Next
End sub

Sub RecalculateRowScore2(sh as Range) *' Object does not support this
property or method error* on this line
For each c in sh.Range("a:a")
If Instr(sh.rng, "-")=0 Then
sh.rng.Offset(0,1).value=Len(sh.rng)*Asc(Mid(sh.rn g,1,1))
ElseIf Instr(sh.rng, "-")0 Then
sh.rng.Offset(0,1).value="-"
End If

<Other codes follow

End if
End sub


Now the rub: why does EXCEL have a problem treating c any differently
from sh in the above examples?


myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Myles,

Dim sh As Worksheet

Having dimmed sh as a worksheet object, you clearly cannot pass it to
another procedure which takes a range object as its single parameter.

Hence the problem you experience:

Sub RecalculateRowScore2(sh as Range) *' Object does not
support this property or method error* on this line

A worksheet is not a range object.

---
Regards,
Norman



"Myles" wrote in
message ...

Hi Norman,
I was musing over what happened when I applied the Call idea to my
project.
Using your last tip-off, the structure of my coding becomes:

Sub MainCode()
Dim sh As Worksheet
Dim c As Range

For each sh in WorkSheets
For each c in sh.Range("a:a")

Call RecalculateRowScore(c)

Next
Next
End sub

Sub RecalculateRowScore(rng as Range)

If Instr(rng, "-")=0 Then
rng.Offset(0,1).value=Len(rng)*Asc(Mid(rng,1,1))
ElseIf Instr(rng, "-")0 Then
rng.Offset(0,1).value="-"
End If

<Other codes follow

End if
End Sub


The foregoing works perfectly (as does your cell colouring example).
However, rather than parse the cell c as argument, it appears
intuitively appealing to plump for parsing the worksheet object sh to
the servient subroutine. This option produces:

Sub MainCode2()
Dim sh As Worksheet
Dim c As Range

For each sh in WorkSheets

Call RecalculateRowScore2(sh)

Next
End sub

Sub RecalculateRowScore2(sh as Range) *' Object does not support this
property or method error* on this line
For each c in sh.Range("a:a")
If Instr(sh.rng, "-")=0 Then
sh.rng.Offset(0,1).value=Len(sh.rng)*Asc(Mid(sh.rn g,1,1))
ElseIf Instr(sh.rng, "-")0 Then
sh.rng.Offset(0,1).value="-"
End If

<Other codes follow

End if
End sub


Now the rub: why does EXCEL have a problem treating c any differently
from sh in the above examples?


myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613

Hi Myles,

Just to add, you may, of course, pass a range which represents all the cells
on the sheet, e.g.:

Set c = sh.cells

---
Regards,
Norman



"Myles" wrote in
message ...

Hi Norman,
I was musing over what happened when I applied the Call idea to my
project.
Using your last tip-off, the structure of my coding becomes:

Sub MainCode()
Dim sh As Worksheet
Dim c As Range

For each sh in WorkSheets
For each c in sh.Range("a:a")

Call RecalculateRowScore(c)

Next
Next
End sub

Sub RecalculateRowScore(rng as Range)

If Instr(rng, "-")=0 Then
rng.Offset(0,1).value=Len(rng)*Asc(Mid(rng,1,1))
ElseIf Instr(rng, "-")0 Then
rng.Offset(0,1).value="-"
End If

<Other codes follow

End if
End Sub


The foregoing works perfectly (as does your cell colouring example).
However, rather than parse the cell c as argument, it appears
intuitively appealing to plump for parsing the worksheet object sh to
the servient subroutine. This option produces:

Sub MainCode2()
Dim sh As Worksheet
Dim c As Range

For each sh in WorkSheets

Call RecalculateRowScore2(sh)

Next
End sub

Sub RecalculateRowScore2(sh as Range) *' Object does not support this
property or method error* on this line
For each c in sh.Range("a:a")
If Instr(sh.rng, "-")=0 Then
sh.rng.Offset(0,1).value=Len(sh.rng)*Asc(Mid(sh.rn g,1,1))
ElseIf Instr(sh.rng, "-")0 Then
sh.rng.Offset(0,1).value="-"
End If

<Other codes follow

End if
End sub


Now the rub: why does EXCEL have a problem treating c any differently
from sh in the above examples?


myles


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=496613