Mike, Patrick

I've completely removed headcount and still no change... Maybe I just have
too many records and the code cannot work faster, or I was stupid enough to
make this code in incorrect order... These are the values of columns/rows:

For columnnumber 3 to columncount €“ (3 to 102) columns in "Main Table"
For countauthorized 3 to countmainrow €“ (3 to 423) rows in "Main Table"
For Matchjob 2 to recordtablecount €“ (2 to 27000) records in "recordtable"


"Mike Fogleman" wrote:

Dim headcount As String
is in the original post

Mike F
"Patrick Molloy" wrote in message
...
maybe test headcount first ... and switch off screenupdating

is headcount DIM'd as long or string? I assume lond, so not = "1" but just
=1

With Worksheets("RecordTable")
For matchjob = 2 To RecordTablerowcount
headcount = .Cells(matchjob, 13).Value

IF headcount = 1 then

jobvalue = .Cells(matchjob, 10).Value
sitevalue = .Cells(matchjob, 3).Value


If (jobvalue = tablejobvalue) And (sitevalue = sitename)
Then
numberofrecords = numberofrecords + 1
End If

end if

Next matchjob
End With


"Tim Zych" <tzych@nospam at earthlink dot net wrote in message
...
I'm stumped.

What happens with various tests with this block of code:

With Worksheets("RecordTable")
For matchjob = 2 To RecordTablerowcount
jobvalue = .Cells(matchjob, 10).Value
sitevalue = .Cells(matchjob, 3).Value
headcount = .Cells(matchjob, 13).Value

If (jobvalue = tablejobvalue) And (sitevalue = sitename)
And (headcount = "1") Then
numberofrecords = numberofrecords + 1
End If
Next matchjob
End With

Worksheets("Main Table").Cells(countAuthorized,
columnnumber).Value = numberofrecords

Such as

With Worksheets("RecordTable")
For matchjob = 2 To RecordTablerowcount
jobvalue = '<some hardcoded jobvalue'
sitevalue = '<some site value'
headcount = '<some head count'

If 1 = 1 Then
numberofrecords = numberofrecords + 1
End If
Next matchjob
End With

' Worksheets("Main Table").Cells(countAuthorized,
columnnumber).Value = numberofrecords


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"MikiV" wrote in message
...
Calculation is off Tim, I tried it several times (after few steps).
I don't get it, it takes like 30min to calculate if I use excel formulas
(sumif, countif etc) but it takes much longer with VB code when actually
should be faster... Doesn't make any sense... Any other ideas???

?application.Calculation = xlCalculationManual
True
True
True
True
True

"Tim Zych" wrote:

From the way you describe it, it looks like calculation is occurring
after
each update, but from your code sample, you have turned calculation
off.

During the line

Worksheets("Main Table").Cells(countAuthorized,
columnnumber).Value
= numberofrecords

is calculation really off? Perhaps some other process has turned it on.

Put a breakpoint on that line, then when the macro stops there, in the
immediate window type.

?application.Calculation =xlCalculationManual

Is it True?



--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"MikiV" wrote in message
...
First of all thank you for your effort and suggestions, I really
appreciate
it... Unfortunately it didn't work... I really don't get it, I'm
loosing
my
mind here, everything looks correct but when I stop the code after
like 10
seconds, only a dozen cells are updated... It doesn't make any
sense... I
have 27000 rows in the other tab, and 13 columns...
Do you have any other ideas????

"Tim Zych" wrote:

Hmm, out of your macro, I would think the code which you say is slow
to
be
some of the faster code you have written.

Generally, to avoid slow code, don't activate or select anything
unless
needed. Here is an alternative way to go about some of the code.

RecordTablerowcount = Worksheets("RecordTable").Cells(2,
1).CurrentRegion.Rows.Count
countMAINrow = Worksheets("Main Table").Cells(3,
1).CurrentRegion.Rows.Count - 1
columncount = Worksheets("Main Table").Cells(3,
3).CurrentRegion.Columns.Count - 4

tablesitecount = 505

For columnnumber = 3 To columncount
sitename = Worksheets("Main Table").Cells(tablesitecount,
2).Value

For countAuthorized = 3 To countMAINrow

tablejobvalue = Worksheets("Main
Table").Cells(countAuthorized,
1).Value

numberofrecords = 0

With Worksheets("RecordTable")
For matchjob = 2 To RecordTablerowcount
jobvalue = .Cells(matchjob, 10).Value
sitevalue = .Cells(matchjob, 3).Value
headcount = .Cells(matchjob, 13).Value
If (jobvalue = tablejobvalue) And (sitevalue =
sitename)
And
(headcount = "1") Then
numberofrecords = numberofrecords + 1
End If
Next matchjob
End With

Worksheets("Main Table").Cells(countAuthorized,
columnnumber).Value
= numberofrecords

Next countAuthorized


How many rows are being evaluated? Are the rows at the beginning of
the
evaluation as slow as those at the end? Even a spreadsheet with
65,000
rows
should be quick. Something is seriously wrong for it to take hours.
I
would
imagine it should take a few seconds max per column.

Just try replacing the "slow part" with this modification.

With Worksheets("RecordTable")
For matchjob = 2 To RecordTablerowcount
jobvalue = .Cells(matchjob, 10).Value
sitevalue = .Cells(matchjob, 3).Value
headcount = .Cells(matchjob, 13).Value
If (jobvalue = tablejobvalue) And (sitevalue =
sitename)
And
(headcount = "1") Then
numberofrecords = numberofrecords + 1
End If
Next matchjob
End With

Does that make any difference? Also, is headcount really a string in
the
spreadsheet, or a number?

Alternatively, try this modification, which evaluates headcount as a
number
rather than a string and should be faster.

Dim headcount As Long

With Worksheets("RecordTable")
For matchjob = 2 To RecordTablerowcount
jobvalue = .Cells(matchjob, 10).Value
sitevalue = .Cells(matchjob, 3).Value
headcount = .Cells(matchjob, 13).Value
If (jobvalue = tablejobvalue) And (sitevalue =
sitename)
And
(headcount = 1) Then
numberofrecords = numberofrecords + 1
End If
Next matchjob
End With


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"MikiV" wrote in message
...
Somebody please help. I have several excel spreadsheets which
should be
combined and calculated in one document. I made macro to perform
this
function but it is extremly slow (takes hours). I have tried
everything,
maybe it just cannot go any faster because of the thousands of
records
that
it has to search and calculate.
Following is a small section of my code (the slowest sub), I have
several
similar to this one. What it should do is go through all the
records in
tab
"RecordTable", search for all matching job codes and locations to
specified
fields in the tab "Main Table", calculate the total count and
input in
specified field in the "Main Table".
The code is correct, it's just way to slow. I have specified the
"slow
part"
below. Thanks in advance for any advice you can give me.

Private Sub CommandButton3_Click()
On Error Resume Next

Dim columncount As Integer
Dim columnnumber As Integer
Dim PCBOErowcount As Integer
Dim countMAINrow As Integer
Dim countAuthorized As Integer
Dim updateAuthorized As String
Dim matchjob As Integer
Dim findcolumns As Integer
Dim tablejobvalue As String
Dim jobvalue As String
Dim tablesitecount As Integer
Dim sitevalue As String
Dim headcount As String
Dim sitename As String
Dim numberofrecords As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Worksheets("RecordTable").Activate
Worksheets("RecordTable").Cells(2, 1).Activate
RecordTablerowcount = ActiveCell.CurrentRegion.Rows.Count