For Each Next Loop behaving in unexpected manner

[MichaelDavid]

Greetings:

I wrote the below macro which uses the For Each Next Loop. The purpose of
the macro is to select a specific cell on each worksheet except the last
worksheet in the workbook. I am surprised that in order for it to work, my
macro has to select the first sheet in the selection before entering the For
Each Next Loop and then use the ActiveSheet.Next.Select instruction in the
For Each Next Loop to move on to the next sheet to be processed. LstRowData
was computed elsewhere and is the last row of the worksheet which contains
the input data.

Sub CheckForEachNext()
'
' Hotkey: Ctrl+Shift+J
'
Dim LstRowData As Long
'Without the below instruction and the ActiveSheet.Next.Select instruction,
only the
' activesheet is processed. With the below instruction, but still without
ActiveSheet.Next.Select, only the first worksheet is processed.

ActiveWorkbook.Worksheets(1).Select
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> "Sheet1" Then
LstRowData = Range("O2")
Cells(LstRowData + 9, "W").Select

' With the below ActiveSheet.Next.Select instruction but without the above
' ActiveWorkbook.Worksheets(1).Select instruction, only the ActiveSheet
through
' the last sheet are processed.

ActiveSheet.Next.Select
Else
Exit For
End If
Next WS
MsgBox "I am done"
End Sub

--
All suggestions will be greatly appreciated. May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[tompl]

Try this:

For Each WS In ActiveWorkbook.Worksheets
If WS.Name <> "Sheet1" Then
LstRowData = Range("O2")
Cells(LstRowData + 9, "W").Select
End If
Next


Tom

 

[MichaelDavid]

Hi Tom:
I like your suggestion. Unfortunately my Excel VBA doesn't. When I ran
your code, only the activesheet had the specified cell selected. When I added
ActiveWorkbook.Worksheets(1).Select before entering the For Each Loop, then
only Worksheets(1) had the specified cell selected. Then when I added
ActiveSheet.Next.Select just after the cell selection, then all the
worksheets had the specified cell selected. Could the implementation of the
For Each Loop itself be defective in my version of Excel? I have the
Microsoft Office Home and Student 2007 version.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[tompl]

The problem is in the definition of LstRowData. Your question did not
explain how it gets it's value. I think you need to put the definition of
LstRowData within the loop. Something like:

For Each WS In ActiveWorkbook.Worksheets

LstRowData = WS.range("O2")

If WS.Name <> "Sheet1" Then
LstRowData = Range("O2")
Cells(LstRowData + 9, "W").Select
End If
Next

Tom

 

[tompl]

Also consider:


WS.Cells(LstRowData + 9, "W").Select

Although I have no idea why you are selecting this cell.

Tom

 

[MichaelDavid]

Hi Tom:
Greetings! The basic problem is that the For Each Next loop will not
proceed to the next worksheet, unless I add that ActiveSheet.Next.Select
instruction. Why should that instruction be necessary? Unless I add that
instruction and also put
ActiveWorkbook.Worksheets(1).Select before entering the For Each Next loop
the macro will not work. Once I add those two instructions, the macro works
perfectly. By selecting the cell Cells(LstRowData + 9, "W") on each sheet, it
is very easy for me to quickly flip through the worksheets and quickly
compare them. As long as LstRowData has a value just before the cell
selection, cell selection works just fine.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[tompl]

I think I get it now. First you have to select the worksheet, then you can
select the cell.

Try this:

For Each WS In ActiveWorkbook.Worksheets
If WS.Name <> "Sheet1" Then
LstRowData = Range("O2") 'if LstRowData was computed elsewhere why is
'this here? should it be WS.Range("O2") = LstRowData? I think this line
'might need to be eliminated here. Or identify what sheet Range("O2") is on.
WS.select
Cells(LstRowData + 9, "W").Select
End If
Next

 

[tompl]

You need to add the line:

WS.select within the loop before the range select.

See my most recent post.

Tom

 

[MichaelDavid]

Hi Tom:
I played around with the code for a few hours, and here is what I came up
with:

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
If WS.Name <> "Sheet1" Then
LstRowData = WS.Range("O2")
WS.Activate
WS.Cells(LstRowData + 9, "W").Select
End If
Next WS

which is very similar to what you came up with.

Apparently the WS.Activate instruction is absolutely necessary. Excel does
not allow
a cell to be selected unless the worksheet it is on has been activated.
Perhaps you can come up with a way to omit WS.Activate instruction. Just
removing the WS.Activate instruction results in the error message: "Run-time
error '1004': Select method of Range class failed." Thanks for all your kind
help.

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Jacob Skaria]

Try the below..

Sub Macro()
Dim ws As Worksheet, lngRow As Long
For intSheet = 1 To Sheets.Count - 1
lngRow = Sheets(intSheet).Cells(Rows.Count, "W").End(xlUp).Row
Application.Goto Sheets(intSheet).Range("G" & lngRow + 9)
Next
Sheets(1).Activate
End Sub

 

[MichaelDavid]

Hi Jacob:
Thank you very much for your kind help. I tried your solution and it
works, but it seems to run in about the same time as the one I devised,
because doing the Goto Sheets(intSheet).Range("G" & lngRow + 9) command
essentially selects the sheets in succession, and, in each case, goes to cell
("G" & lngRow + 9) on that sheet, thus leaving that cell selected. Please
let me know of the advantages of your solution over mine. If the advantages
strike me as significant (such as less error prone, actually faster, lesser
drain on resources, etc.), I will definitely use your solution rather than
mine. But I was really hoping that there might be a way of having the cell
selected on each sheet without actually selecting or going to that sheet
which really slows things down.

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[MichaelDavid]

Hi Jacob:
I just noticed a nice advantage of your method: By using the "For intSheet
= 1 To Sheets.Count - 1" instruction, you eliminate the necessity of my If
Then Else Loop. Nice going!
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Jacob Skaria]

Check out the whether the below makes it any faster...


Dim ws As Worksheet, wsActive As Worksheet, lngRow As Long
Set wsActive = ActiveSheet
Application.ScreenUpdating = False
For intSheet = 1 To Sheets.Count - 1
lngRow = Sheets(intSheet).Cells(Rows.Count, "W").End(xlUp).Row
Application.Goto Sheets(intSheet).Range("W" & lngRow + 9)
Next
wsActive.Activate
Application.ScreenUpdating = True

 

[MichaelDavid]

Hi Jacob:
Thanks for all your help and suggestions. Yes, the code runs very
quickly. Here is what I finally settled on--works perfectly:

Dim intSheet As Long
Application.ScreenUpdating = False
For intSheet = 1 To Sheets.Count - 1
Application.Goto Sheets(intSheet).Range("W" & Sheets(intSheet).Range("O2") +
9)
Next
Application.ScreenUpdating = True
Sheets(1).Activate

It selects the desired cell on each sheet. (On some sheets there is a lot of
data; on others just a little.) And it ends up with Sheets(1) activated so I
can quickly flip through the sheets in order comparing data.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick