Slow execution changing cell value

[Nate]

I've got the code below that loops through a range of cells fills in
some information for a tournament, including assigning a unique code
for a section judge (i.e. J-AB1). Everything works great, except that
it's extremely slow. I tested the speed of each line of code using a
stopwatch sub that others have posted. Each line runs between 0-4
milliseconds except the line: JdgCell = "J-" & c & i which runs
upward of 320 milliseconds. All it's doing is changing the value of a
cell so I don't understand what the holdup is. I'm using almost the
exact same code in another part of the program and it runs fast. Any
ideas?

Thanks! Nate


For Each c In SCodes
With c.Offset(, 16)

If .Value > 0 Then 'check if number of judges > 0
For i = 1 To .Value
Set JdgCell = JdgCell.Offset(1)

'fill in school code
JdgCell.Offset(, -2) = c.Text

'fill in school name
JdgCell.Offset(, -1) = c.Offset(, 1).Text

'fill in judge code
JdgCell = "J-" & c & i <---- this line is taking
300+ milliseconds to execute

Next i
End If
End With
Next c

 

[egun]

The only thing I notice is that you are using just "c" instead of "c.text"
on that last line...

 

[Nate]

I even tried commenting out the variables like:
JdgCell = "J-"
and it still took a long time to execute. <shrug>

 

[Tim Zych]

Is calculation on Automatic? Maybe a recalc is occurring.

Disable calculation to see if that helps:

Dim OrigCalc As Long
OrigCalc = Application.Calculation
Application.Calculation = xlCalculationManual
' ...Code
Application.Calculation = OrigCalc

Also, one suggestion. Use c.Value rather than c.Text. The Text property
returns what is displayed in the cell, not necessarily the underlying value.
It is also slow.

To see how this can be a problem:

Type a number in a cell.
Format it as Accounting style
Run this macro
Sub test()
MsgBox "'" & ActiveCell.Text & "'"
MsgBox "'" & ActiveCell.Value & "'"
End Sub

Single quotes are used to show how padding is included too. The Value
property returns the real underlying value. The Text property returns what
is displayed in the cell.

Use Text if you only want to return what is literally displayed in the cell.
This includes #### which is displayed in Excel when the column is too
narrow. Value returns the underlying value, not ####.

Here is how I would imagine it should look with those changes (untested)

Dim OrigCalc As Long
OrigCalc = Application.Calculation
Application.Calculation = xlCalculationManual
' Declare all other variables
For Each c In sCodes
With c.Offset(, 16)
If .Value > 0 Then
For i = 1 To .Value
Set JdgCell = JdgCell.Offset(1)
JdgCell.Offset(, -2).Value = c.Value
JdgCell.Offset(, -1).Value = c.Offset(, 1).Value
JdgCell.Value = "J-" & CStr(c.Value) & CStr(i)
Next i
End If
End With
Next c
Application.Calculation = OrigCalc

 

[Nate]

Tim,

Turning off the calculation worked! I guess I didn't realize (or
forgot) that there were formulas 15 sheets away that were pulling from
that column, so as a new value got filled in it had to recalc.

Thanks for the explanation between .value and .text as well.
Admittedly, I have been a little confused by the difference between
the two - some reference manuals don't help. But it's crystal clear
now.

Thanks again!!
Nate