Slow run time (2007)

Topic

New Reply

Good day,

My IT director created this snippet of code and asked that I insert it in a much larger project. This snippet seems to take forever to run. Can someone take a look and see if there is a way to make it run faster. It looks to me as if he has coded this to go top down and it runs through every row of the excel file. I am running 2007 and there are 1 million rows but there is data in less than 2000 of them. HELP!!!

Dim c As Range
Dim intHold As Integer
Dim strFormula As String
Dim strURL As String

strURL = “http://www.yourpage.com/por/s/process/CompPORES.cfm?Number=”

For Each c In Range(“J:J”)

If IsNumeric(c.Value) And (c.Value > 0) Then
intHold = c.Value
c.Formula = “=HYPERLINK(“”” & strURL & intHold & “””, “”” & intHold & “””)”
End If

Next c

‘ end of Hyperlink code
End Sub

Reply | Quote

Replies

The code does indeed check each and every one of the more than 1,000,000 cells in column J. You can change it as follows:

For Each c In Range(“J:J”).SpecialCells(xlCellTypeConstants, xlNumbers)
If c.Value > 0 Then
…
End If
Next c

The SpecialCells method returns a range that consists of cells with a numeric value. You only have to check whether the value is positive.

Reply | Quote

Hans,
Thank you. As always you have a quick an elegant solution. I have applied this and have run into one small glitch. There is the possibility that column ‘J’ may have no numeric entries, only text entries. When I ran it on a report that had no numeric entries I received a runtime 1004 error: No cells were found.

Is there a modification I can make to the code that will allow for this situation?

Reply | Quote

How about something like this (warning air code…):

Dim rng as range
on error resume next
set rng = Range("J:J").SpecialCells(xlCellTypeConstants, xlNumbers)
on error goto 0
if not rng is nothing then
For Each c In rng
    If c.Value > 0 Then
        ...
    End If
Next c
end if

Steve

Reply | Quote

You could test like this:

Dim rng As Range
‘ Suppress error messages
On Error Resume Next
Set rng = Range(“J:J”).SpecialCells(xlCellTypeConstants, xlNumbers)
‘ Get out if an error occurred, i.e. if there are no numeric entries
If Err Then Exit Sub
‘ Otherwise, continue normally
On Error GoTo 0
For Each c In rng
…

Reply | Quote