converting a for next loop into a for each loop

[Matthew Dyer]

I've read that a for each loop is more efficient when working with
large tables of data. Below is my for next loop that i've been using
to do some basic things. I am trying to convert it into a for each
loop. The difficulty i'm having is that as you can see i use my i
variable to identify the 'row' value for what cells i'm looking to
modify. How would i go about using the for each loop and get over this
hiccup?

Sub looop()
'define stale value
sstale = Date - 5
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'build time zone column
cells(1, "j").Value = "Time Zone"
With Range("j1")
.Font.Bold = True
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
End With

For i = 2 To cells(Rows.Count, "e").End(xlUp).Row

'determine which timezone the account is in. Column E holds the value
for state
If Not IsError(Application.Match(cells(i, "f").Value, Array("CA",
"WA", "OR", "NV"), 0)) Then
cells(i, "j").Value = "4-Pacific"
End If

If Not IsError(Application.Match(cells(i, "f").Value, Array("AZ",
"UT", "NM", "CO", "ID", "WY", "MT"), 0)) Then
cells(i, "j").Value = "3-Mountain"
End If

If Not IsError(Application.Match(cells(i, "f").Value, Array("TX",
"LA", "MS", "AL", "IL", "AR", "OK", "KS", "NE", "MO", _
"IA", "SD", "ND", "MN", "WI"), 0)) Then
cells(i, "j").Value = "2-Central"
End If

If Not IsError(Application.Match(cells(i, "f").Value, Array("DC",
"FL", "GA", "TN", "KY", "IN", "MI", "OH", "WV", "SC", "NC", _
"VA", "MD", "PA", "NY", "DE", "NJ", "CT", "RI", "MA", "VT", "NH",
"ME"), 0)) Then
cells(i, "j").Value = "1-East"
End If

If Not IsError(Application.Match(cells(i, "f").Value, Array("HI",
"AK"), 0)) Then
cells(i, "j").Value = "5-Other"
End If

'large bal format
If cells(i, "h") > 19999 Then
cells(i, "h").Interior.ColorIndex = 3
cells(i, "h").Font.Bold = True
End If

'stale format
If cells(i, "i") <= sstale Then
cells(i, "i").Interior.ColorIndex = 36
cells(i, "i").Font.Bold = True
End If

'DC format
If cells(i, "k") >= Date Then
cells(i, "k").Interior.ColorIndex = 4
cells(i, "k").Font.Bold = True

Else
cells(i, "k").ClearContents
End If

'active repo
If cells(i, "n") <> "Y" Then
cells(i, "m").ClearContents
End If

'Early Pay Defualt
If cells(i, "l") < 6 Then
cells(i, "l").Interior.ColorIndex = 7
cells(i, "l").Font.Bold = True
End If

Next i
Columns("n").ClearContents

End Sub

 

[GS]

Matthew,
I don't recommend you go there in this case scenario. Doing so will
require using object ref syntax, a counter, and typing more code than
what you have already.

As for your assertion that 'For...Each...Next' is more efficient than
'For...Next', I disagree: the former adds to runtime overhead more than
the latter because of the addition 'dot' processing AND required use of
a Variant or Object type for the 'Each' object<IMO>.

 

[Matthew Dyer]

Matthew,
I don't recommend you go there in this case scenario. Doing so will
require using object ref syntax, a counter, and typing more code than
what you have already.

As for your assertion that 'For...Each...Next' is more efficient than
'For...Next', I disagree: the former adds to runtime overhead more than
the latter because of the addition 'dot' processing AND required use of
a Variant or Object type for the 'Each' object<IMO>.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks for the help Garry. Glad to know i'm not crazy.