Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
Also consider:
WS.Cells(LstRowData + 9, "W").Select Although I have no idea why you are selecting this cell. Tom |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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" wrote: Also consider: WS.Cells(LstRowData + 9, "W").Select Although I have no idea why you are selecting this cell. Tom |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
You need to add the line:
WS.select within the loop before the range select. See my most recent post. Tom |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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 "tompl" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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 -- Jacob "MichaelDavid" wrote: 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 "tompl" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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 "Jacob Skaria" wrote: 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 -- Jacob "MichaelDavid" wrote: 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 "tompl" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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" wrote: 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 -- Jacob "MichaelDavid" wrote: 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 "tompl" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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 -- Jacob "MichaelDavid" wrote: 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" wrote: 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 -- Jacob "MichaelDavid" wrote: 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 "tompl" wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next Loop behaving in unexpected manner
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 "Jacob Skaria" wrote: 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 -- Jacob "MichaelDavid" wrote: 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" wrote: 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 -- Jacob "MichaelDavid" wrote: 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 "tompl" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data from SQL to Excel in a formated manner.. | Excel Programming | |||
comapre two list of data & arranging in a same manner | New Users to Excel | |||
How do I add up row sums in a systematic manner? | Excel Programming | |||
shell to behave in synchronous manner | Excel Programming | |||
macro needed for sorting txt in a ceratian manner | Excel Programming |