Hi, I have a lot of records to search thru', everything works fine,
its just this section of code below is far too slow.

Is there any way to make this section of code work at sub light speed
or even light speed?

thanks for any help

Ste

Do While Not (Sheets("Meetingstodate").Cells(xlrow, 2).Value = "")

Set StartCell = Sheets("Meetingstodate").Cells(xlrow, 2)
Set EndCell = Sheets("Meetingstodate").Cells(xlrow, 7)

For Each cell In Range(StartCell, EndCell)

If cell.Value = A Then
intcounter1 = 1
End If
If cell.Value = B Then
intcounter2 = 1
End If
If cell.Value = C Then
intcounter3 = 1
End If
If cell.Value = D Then
intcounter4 = 1
End If
If cell.Value = E Then
intcounter5 = 1
End If
If cell.Value = F Then
intcounter6 = 1
End If

Next cell

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

xlrow = xlrow + 1

Loop

Light speed is tough!

But rather than using cell.Value repeatedly, use a correctly Dimm'ed variable:
v=cell.Value

Also use a Case in place of the bunch of Ifs. That way the extra statements
don't need to be executed.
--
Gary''s Student - gsnu2007k


"ste mac" wrote:

Hi, I have a lot of records to search thru', everything works fine,
its just this section of code below is far too slow.

Is there any way to make this section of code work at sub light speed
or even light speed?

thanks for any help

Ste

Do While Not (Sheets("Meetingstodate").Cells(xlrow, 2).Value = "")

Set StartCell = Sheets("Meetingstodate").Cells(xlrow, 2)
Set EndCell = Sheets("Meetingstodate").Cells(xlrow, 7)

For Each cell In Range(StartCell, EndCell)

If cell.Value = A Then
intcounter1 = 1
End If
If cell.Value = B Then
intcounter2 = 1
End If
If cell.Value = C Then
intcounter3 = 1
End If
If cell.Value = D Then
intcounter4 = 1
End If
If cell.Value = E Then
intcounter5 = 1
End If
If cell.Value = F Then
intcounter6 = 1
End If

Next cell

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

xlrow = xlrow + 1

Loop

Thanks Gary''s Student, can you please post an example.

Ste

Something like:

'Dim v as whatever A,B,C,... are
v = cell.Value
Select Case v
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

The speed will improve a little. Like I said; light speed is tough.
--
Gary''s Student - gsnu2007k


"ste mac" wrote:

Thanks Gary''s Student, can you please post an example.

Ste

Thanks Gary''s Student, I just tried your improvement and it it
definately quicker no doubt about it.

A welcome improvement.

I will have another trawl through this ng and see if it cab be further
improved.

Thanks alot...

Ste

Do
set WhatFound=Range(StartCell, EndCell).Find(A, LookIn:=xlValues,
LookAt:=xlWhole, MatchCase:=False)
If Not (WhatFound Is Nothing) then
'Action A
End If
Loop Until WhatFound Is Nothing
Do
set WhatFound=Range(StartCell, EndCell).Find(B, LookIn:=xlValues,
LookAt:=xlWhole, MatchCase:=False)
If Not (WhatFound Is Nothing) then
'Action B
End If
Loop Until WhatFound Is Nothing

I don't quite understand why your totcounter counter gets incremented that
way, but in general if you want to loop values of a large number of cells,
assign the entire range.Value to an array and loop the array

arr = Range("A1:D100").Value

for r = 1 to ubound(arr)
for c = 1 to ubound(arr, 2)

select case arr(r, c)
case A: intcounter1 = 1 ' ?
etc
end select
' code
next
next

Perhaps in your case though you could use the CountIf worksheet function for
each of your A, B, C values

Regards,
Peter T


"ste mac" wrote in message
...
Hi, I have a lot of records to search thru', everything works fine,
its just this section of code below is far too slow.

Is there any way to make this section of code work at sub light speed
or even light speed?

thanks for any help

Ste

Do While Not (Sheets("Meetingstodate").Cells(xlrow, 2).Value = "")

Set StartCell = Sheets("Meetingstodate").Cells(xlrow, 2)
Set EndCell = Sheets("Meetingstodate").Cells(xlrow, 7)

For Each cell In Range(StartCell, EndCell)

If cell.Value = A Then
intcounter1 = 1
End If
If cell.Value = B Then
intcounter2 = 1
End If
If cell.Value = C Then
intcounter3 = 1
End If
If cell.Value = D Then
intcounter4 = 1
End If
If cell.Value = E Then
intcounter5 = 1
End If
If cell.Value = F Then
intcounter6 = 1
End If

Next cell

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

xlrow = xlrow + 1

Loop

I don't quite understand your logic and I know this code is not the same
logic as yours.
but i think this one might be a little faster than yours.

Sub test()
Dim xlrow As Long, lastrow As Long
Dim totcounter As Long, s As Long
Dim Startcell As Range, Endcell As Range, rng As Range
Dim r

xlrow = 2
Set Startcell = Cells(xlrow, 2)
Set Endcell = Cells(xlrow, 7)
Set rng = Range(Startcell, Endcell)

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

For xlrow = 2 To lastrow
For Each r In Array("A", "B", "C", "D", "E", "F")
If Application.CountIf(rng, r) = 1 Then
s = s + 1
End If
Next
If s = 5 Then
totcounter = totcounter + 1
End If
s = 0
Set rng = rng.Offset(1, 0)
Next
MsgBox totcounter
End Sub

keiji

"ste mac" wrote in message
...
Hi, I have a lot of records to search thru', everything works fine,
its just this section of code below is far too slow.

Is there any way to make this section of code work at sub light speed
or even light speed?

thanks for any help

Ste

Do While Not (Sheets("Meetingstodate").Cells(xlrow, 2).Value = "")

Set StartCell = Sheets("Meetingstodate").Cells(xlrow, 2)
Set EndCell = Sheets("Meetingstodate").Cells(xlrow, 7)

For Each cell In Range(StartCell, EndCell)

If cell.Value = A Then
intcounter1 = 1
End If
If cell.Value = B Then
intcounter2 = 1
End If
If cell.Value = C Then
intcounter3 = 1
End If
If cell.Value = D Then
intcounter4 = 1
End If
If cell.Value = E Then
intcounter5 = 1
End If
If cell.Value = F Then
intcounter6 = 1
End If

Next cell

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

xlrow = xlrow + 1

Loop