Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there something faster than select case?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there something faster than select case?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there something faster than select case?
Thanks Ron. I'II try it now
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there something faster than select case?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there something faster than select case?
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there something faster than select case?
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there something faster than select case?
Thanks to you both... its working, now I'II see if it has speeded up
any Thanks a lot Ron and Keiji Ste |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there something faster than select case?
I am confused by that code. What exactly is the processing logic there?
-- __________________________________ HTH Bob wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
End Select without Select Case, Block If without End If errors | Excel Programming | |||
Case Select | Excel Programming | |||
Can faster CPU+larger/faster RAM significantly speed up recalulati | Excel Discussion (Misc queries) |