ExcelBanter - View Single Post - Is there something faster than select case?

Ron Rosenfeld

On Mon, 1 Sep 2008 02:40:40 -0700 (PDT), wrote:

With help/advice from Peter T and kounoike (Thankyou). I have put
this code together.

Can it be cut down even more?... to make it faster

Is there a way to count if there are 5 or more in a range without
using 'select case'
or not using the 'intcounter' bit bit of code?

Thanks for any help/suggestions

Ste
................................................. ..........................

Set Startcell = Sheets("meetingstodate").Cells(xlrow, 2)
Set Endcell = Sheets("meetingstodate").Cells(xlrow, 7)
Set rng = Range(Startcell, Endcell)

lastrow = Sheets("meetingstodate").Cells(xlrow, 2).End(xlDown).Row

For xlrow = 2 To lastrow

arr = Range(Startcell, Endcell).Value

For j = 1 To UBound(arr)

For k = 1 To UBound(arr, 2)

Select Case arr(j, k)
Case A
intcounter1 = 1
Case B
intcounter2 = 1
Case C
intcounter3 = 1
Case D
intcounter4 = 1
Case E
intcounter5 = 1
Case F
intcounter6 = 1
End Select

Set rng = rng.Offset(1, 0)

If intcounter1 + intcounter2 + intcounter3 + intcounter4 +
intcounter5 + intcounter6 = 5 Then
totcounter = totcounter + 1
End If

Next
Next
Next

If totcounter <= 2 Then
totcounter = 0
End If
If totcounter = 3 Then
totcounter = 0
'carry on with next execution

Perhaps CountIf would be faster?
I would step through an array to check each letter's count; and also use
intcounter as an array to store the results of the count.

=======================================
Dim arr As Variant
Dim intcounter(0 To 5)
Dim Rng As Range
Dim i As Long

Set Rng = Range(Startcell, Endcell)
arr = Array("A", "B", "C", "D", "E", "F")

For i = 0 To 5
intcounter(i) = Application.WorksheetFunction.CountIf(Rng, arr(i))
Next i
========================================
--ron