Best Way to Loop Through Range?

[Saladsamurai]

Okay then

I have attached a sample WorkSheet, though it's probably not necessary.

The sheet has a bunch of cells (duh...) that contain either a "$" or a "c" or a number as a Percentage.

I would like to write a code that Loops through the Range and puts the percentages into Classes, like for a Histogram.

I would like to store the frequency of each class in a variable.

The classes can be as simple as:
[vba]
Over_90 = # of times >= 90%

From_80to90 = # of times from 80% to 90%

From_70to80 = # of times from 70% to 80%

Under_70 = # of times < 70 %
[/vba]

I was going to use a Nested For-Loop with a Select Case inside of it.

But I was not sure if there was a FASTER/More Efficient way then Looping

Thanks!!

[nst1107]

Looping is the best option I can think of. There is a histogram function you can use if you have the Analysis ToolPak VBA addin installed, but it would present complications.

[Saladsamurai]

Really? For Loops seem pretty slow to me. There isn't some combination of using a Range and count functions?

I don't know if that is any quicker, nor do I know how to go about setting up the syntax.

[nst1107]

You're right. It does seem pretty slow. Here's a sample routine I set up to run over 720,000 records. It takes a fraction over 7 seconds. Don't know if that's slow to you or not.[VBA]Option Explicit
Sub a()
Dim startTime
Dim c As Range
Dim str As Long
Dim Over_0 As Long, Over_5 As Long, Over_10 As Long, Over_15 As Long
startTime = Timer
For Each c In Range("A1:A720000")
On Error Resume Next
If InStr(c, "%") <> 0 Then
str = CLng(Left(c, Len(c) - 1))
Select Case str
Case Is < 6
Over_0 = Over_0 + 1
Case 6 To 10
Over_5 = Over_5 + 1
Case 11 To 15
Over_10 = Over_10 + 1
Case 16 To 20
Over_15 = Over_15 + 1
End Select
End If
Next
MsgBox "Over 0 = " & Over_0 & Chr(13) & "Over 5 = " & Over_5 & Chr(13) & "Over 10 = " & Over_10 & _
Chr(13) & "Over 15 = " & Over_15 & Chr(13) & "Time elapsed: " & Timer - startTime & " seconds"
End Sub[/VBA]There are a lot of people on this forum that I believe could answer your question much better. This is the best I know.