Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Slow run time (2007)

    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
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Slow run time (2007)

    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.

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow run time (2007)

    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?
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Slow run time (2007)

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

    <pre>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</pre>


    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Slow run time (2007)

    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
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •