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

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

tablesitecount = 505
Worksheets("Main Table").Activate
Worksheets("Main Table").Cells(3, 3).Activate

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

''''''' Slow Part

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

''''''' End of Slow Part

Next matchjob

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

Next countAuthorized
tablesitecount = tablesitecount + 1
columnnumber = columnnumber + 3
Next columnnumber

ActiveWorkbook.Save

End Sub