Is there something faster than select case?


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
 
Ad

Advertisements

Joined
Apr 29, 2008
Messages
66
Reaction score
0
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.
 
R

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
 
S

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
 
Ad

Advertisements

R

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
 
K

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
 
S

stemac

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

Thanks a lot Ron and Keiji

Ste
 
Ad

Advertisements

K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top