For Each Loop

[Leon]

Hello Everyone,

I am just getting into creating a loop in VBA and i was wondering to
have the same macro run on every sheet is the For Each loop the right
tool to use? And if yes, how do i tell it to do that?

This is my current macro:
If Range("B13").Value = Date Then
Application.Run ("TabRed")
ElseIf Range("B13").Value < Date Then
Application.Run ("TabRed")
ElseIf Range("B13").Value < Date + 30 Then
Application.Run ("TabYellow")
Else
Application.Run ("TabWhite")
End If

I still want to add to the IF macro but i also want it to run on all
worksheets. Anyone have tips on how to make this possible?

Thanks for the help. : )

 

[Gary''s Student]

If you want to do something across all the worksheets in a workbook then For
Each is suitable.


Suppose you want to calculate the sum of all the A1 cells in a workbook:


Sub Macro1()
Dim w As Worksheet
t = 0
For Each w In Worksheets
w.Activate
t = t + Cells(1, 1)
Next
MsgBox (t)
End Sub

 

[Leon]

Gary's Student,

Is there any chance you can explain the commands to me so that i can
modify them? For example what Dim w As Worksheets does and t=0 ...?
Sorry about that i am just trying to figure it out and i just get lost.

Thanks

 

[Tushar Mehta]

If you need help understanding what t=0 means you should consider getting an
introductory book on programming, preferably one that has a VB(A) focus.

That said the code you were given can be improved. There is little need to
activate and select objects.

Option Explicit
Sub Macro1()
Dim w As Worksheet, Rslt As Double
Rslt = 0
For Each w In ActiveWorkbook.Worksheets
Rslt = Rslt + w.Cells(1, 1)
Next w
MsgBox (Rslt)
End Sub

or, leveraging XL's object model, the oneliner below. Of course, it will
fail under certain conditions such as no open workbook but that is another
intermediate/advanced lesson.

Option Explicit

Sub Macro2()
MsgBox Application.Evaluate("SUM(" & Worksheets(1).Name & ":" _
& Worksheets(Worksheets.Count).Name & "!A1)")
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Leon]

Thanks Tushar actually this is the new code i am using which seems to
work partialy well:

Sub test()

Dim ws As Worksheet, rng
rng = Array(13, 16, 22, 27)
For Each ws In Worksheets
x = Application.Match(ws.Name, Array("as", "AT&T Lease"), 0)
If Not IsError(x) Then
'change to "If IsError(x) Then to make it search all except what is
listed above
With ws
For i = 0 To UBound(rng)
flag = False
Select Case .Range("b" & rng(i)).Value
Case Is <= Date
.Tab.ColorIndex = 3
' Application.Run ("TabRed"): flag = True
Case Is < Date + 30
.Tab.ColorIndex = 6
'Application.Run ("TabYellow"): flag = True
Case Else
.Tab.ColorIndex = -4142
'Application.Run ("TabWhite"): flag = False
End Select
If flag Then Exit For
Next
End With
End If
Next
End Sub

The Tab Red macro is:
ActiveWorkbook.Sheets("AT&T Lease").Tab.ColorIndex = 3

What i can't figure out now is how to change the tab red macro to not
be a specific sheet but the sheet the loop is checking.

What the loop does is check 4 cells in specific sheets. Those cells
have dates in them and if any of them = today i want the sheet tab to
be red and so on. If one of the cells meet the criteria i have it stop
the loop and go to the next sheet.

I hope that all made some kind of sence. Any ideas? or Things i
should clarify?

Thanks alot for the help i really appreciate it.