Problems Using For Each Loop with Worksheet Array Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RyanH - 09 Oct 2007 02:36 GMT

I am a novice to Excel VBA and would like to be pointed in the right
direction to solving the problem I am having with the code below. I want to
use the For...Next i Loop for each Worksheet in my Workbook. For some reason
the For Each...Next sh Loop is not working for the worksheets I specified.
The code only works if I physically select each individual worksheet and run
it once per worksheet. PLEASE HELP!! HUGE THANKS IN ADVANCE!!

Sub NEW_COMPILE_SCHEDULES()

Dim sh As Worksheet
LastRow = Cells(65536, 1).End(xlUp).Row

For Each sh In Worksheets(Array("Engineering", "Graph Pro", "Metal Fab",
"Alum Ext"))
For i = LastRow To 5 Step -1
If Cells(i, 10).Font.ColorIndex = 15 Then
Cells(i, 1).EntireRow.Delete
ElseIf Cells(i, 10) = "" Then
Cells(i, 1).EntireRow.Delete
End If
Next i
Next sh

End Sub

Reply to this Message

Earl Kiosterud - 09 Oct 2007 03:22 GMT

Ryan

This statement
If Cells(i, 10).Font.ColorIndex = 15 Then
doesn't care which sh the for-each loop is currently working with. It's unqualified, so it
uses the currently active sheet, not sh. It needs to be
If sh.Cells(i, 10).Font.ColorIndex = 15 Then

Same for the other ones.

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

>I am a novice to Excel VBA and would like to be pointed in the right
> direction to solving the problem I am having with the code below. I want to
[quoted text clipped - 20 lines]
>
> End Sub

Reply to this Message

Roger Govier - 09 Oct 2007 08:29 GMT

Ryan

In addition to Earl's advice, lastrow is being set outside of the For loop.

Lastrow will be set for whatever sheet is active at the time the sub is
invoked
Is this what you want?
or do you want

Dim Sh as worksheet, lastrow as long

For Each sh In Worksheets(Array("Engineering", "Graph Pro", "Metal Fab",
"Alum Ext"))
LastRow = sh.Cells(65536, 1).End(xlUp).Row

Signature

Regards
Roger Govier

> Ryan
>
[quoted text clipped - 34 lines]
>>
>> End Sub

Reply to this Message

RyanH - 09 Oct 2007 20:28 GMT

I used Earl's advice and your advice as well, but for some reason when I run
the macro it is deleting rows that have black text. It should only delete
entire rows if the cell is blank or grey text in colomn "J". I assigned the
macro to a command button on Sheet8. If I run the macro while I am looking
at "Engineering" it works great for "Engineering" but screws the rest of the
worksheets up. I have looked at this for hours and can't figure it out, any
idea what could be happening? I listed the present code below:

Sub NEW_COMPILE_SCHEDULES()

Dim sh As Worksheet
Dim LastRow As Long

For Each sh In Worksheets(Array("Engineering", "Graph Pro", "Metal Fab",
"Alum Ext", _
"Custom Fab", "Electrical", "Ch Ltrs", "Foam Fab", "Metal
Paint", _
"Thermo", "Tri Graphics", "Deco Faces", "Tri-Face", "LED",
"Crating", _
"Service", "Delivery"))
LastRow = sh.Cells(65536, 1).End(xlUp).Row
For i = LastRow To 5 Step -1
If sh.Cells(i, 10).Font.ColorIndex = 15 Then
sh.Cells(i, 1).ENTIREROW.Delete
ElseIf Cells(i, 10) = "" Then
sh.Cells(i, 1).ENTIREROW.Delete
End If
Next i
Next sh

End Sub

> Ryan
>
[quoted text clipped - 49 lines]
> >>
> >> End Sub

Reply to this Message

Earl Kiosterud - 10 Oct 2007 00:27 GMT

Ryan,

You left off one qualifier (sh.) in the ElseIf line.

Signature

>I used Earl's advice and your advice as well, but for some reason when I run
> the macro it is deleting rows that have black text. It should only delete
[quoted text clipped - 81 lines]
>> >>
>> >> End Sub

Reply to this Message

RyanH - 10 Oct 2007 20:55 GMT

BAM! PERFECT! This is my first attempt at writing a macro from scratch. I
appreciate your help.

> Ryan,
>
[quoted text clipped - 85 lines]
> >> >>
> >> >> End Sub

Reply to this Message