Is there something faster than select case?

[stemac]

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

 

[Ron Rosenfeld]

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

 

[stemac]

Thanks Ron. I'II try it now

 

[Bob Phillips]

I am confused by that code. What exactly is the processing logic there?

 

[stemac]

Ron, not quite there, have I got something wrong?
I may have the wrong syntax with the 'IF' statement...
But, I have removed the "" from around the letters as the letters are
integers that are created before this part of the macro

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

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

For xlrow = 2 To lastrow

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

Set Rng = Rng.Offset(1, 0)

If intcounter(i) >= 5 Then ''''''''''' is this correct syntax?
totcounter = totcounter + 1
End If

Next

 

[Ron Rosenfeld]

Ron, not quite there, have I got something wrong?
I may have the wrong syntax with the 'IF' statement...
But, I have removed the "" from around the letters as the letters are
integers that are created before this part of the macro

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

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

For xlrow = 2 To lastrow

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

Set Rng = Rng.Offset(1, 0)

If intcounter(i) >= 5 Then ''''''''''' is this correct syntax?
totcounter = totcounter + 1
End If

Next

Perhaps I misunderstood something in your post.

1. I guess your letters are named variables. That shouldn't make a difference.
2. Are you checking the count in each row separately? And then taking an
action depending on that row containing 5 or more of [A-F]? And not interested
in the individual counts?

If so, you could perhaps simplify:

------------------------
For i = 0 To 5
intcounter = intcounter + Application.WorksheetFunction.CountIf(Rng,arr(i))
Next i
if intcounter >= 5 then totcounter=totcounter + 1
-----------------------

3. In the code you posted above, you never change the range you are checking.
The For xlrow ... line doesn't seem to do anything. So if you are trying to
step through individual rows, as opposed to just checking your entire range,
then you need to rework that code.
--ron

 

[keiji kounoike]

Ron, not quite there, have I got something wrong?
I may have the wrong syntax with the 'IF' statement...
But, I have removed the "" from around the letters as the letters are
integers that are created before this part of the macro

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

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

For xlrow = 2 To lastrow

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

Set Rng = Rng.Offset(1, 0)

If intcounter(i) >= 5 Then ''''''''''' is this correct syntax?
totcounter = totcounter + 1
End If

Next

I'm not sure what your logic is, but in my guess this might be what you
want to do, although i don't know it's efficient as you thought.


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
arr = Array(A, B, C, D, E, F)

For xlrow = 2 To lastrow
intcounter = 0

For i = 0 To 5
intcounter = intcounter + _
Application.WorksheetFunction.CountIf(rng, arr(i))
Next i

Set rng = rng.Offset(1, 0)

If intcounter >= 5 Then
totcounter = totcounter + 1
End If

Next

keiji

 

[stemac]

Thanks to you both... its working, now I'II see if it has speeded up
any

Thanks a lot Ron and Keiji

Ste

 

[keiji kounoike]

Thanks to you both... its working, now I'II see if it has speeded up
any

Thanks a lot Ron and Keiji

Ste

Seeing your select part in your code in your first post,

Select Case arr(j, k)
Case A
intcounter1 = 1
Case B
intcounter2 = 1

i think the code below in my post

For i = 0 To 5
intcounter = intcounter + _
Application.WorksheetFunction.CountIf(rng, arr(i))
Next i

should be

For i = 0 To 5
If Application.WorksheetFunction.CountIf(rng, arr(i))>0 Then
intcounter = intcounter + 1
End If
Next i

in my thought.

Keiji