Is there something faster than select case?

  • Thread starter Thread starter stemac
  • Start date Start date
S

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
 
Maybe I'm missing something but why not use COUNTIF?
Have a set of cells with the formula =IF(COUNTIF(MyRange, A)>0, 1, 0)
where MyRange is StartCell:EndCell and A is changed to B etc,
and another to total these and it will all be done in the worksheet without your having to use any code. This will almost certainly be quicker.
 
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
Click to expand...

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
 
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, 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
Click to expand...

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
 
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
Click to expand...

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
 
Thanks to you both... its working, now I'II see if it has speeded up
any

Thanks a lot Ron and Keiji

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

Thanks a lot Ron and Keiji

Ste
Click to expand...

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
 
Back
Top