I have a section of code that creates hyperlinks to web pages in a spreadsheet. The code has worked perfectly for years with the two conditions that were in place. I now have a third condition I have to test for and the code is not handling this well. I am getting an error that there is a next without a for. The for is there so I don't understand the error. I am hoping someone has a simple solution for me.
Here is the code that I am using.
Code: Select all
Sub insert_hyperlink()
' code to change POR column (column J) to Hyperlink field if appropriate value exists
' added by cmcclary 9/8/2008
'
' Modified for the 8/2/13 design changes
'
' updated for DPO System 11/21/2022
'
' Set strURL to the appropriate base URL which points to the lookup tool within the POR system
' For column k loop through all values
' If the value in the current cell is numeric and greater than zero then put the value in a variable
' Set the cells formula to the HPERLINK formula using the concat of URL and the original value of the cell
Dim c As Range
Dim intHold As Long
Dim strFormula As String
Dim strURL As String
Dim strDPO As String
Dim strDPO2 As String
Dim strDPO23 As String
Dim strDPO24 As String
Dim rng As Range
Dim LastRow As Long
strURL = "http://www.recsport.indiana.edu/s/allstaff/por/s/process/CompPORES.cfm?Number="
strDPO23 = "https://pdf.digitalpurchaseorder.com/22239/p-o-"
strDPO24 = "https://pdf.digitalpurchaseorder.com/24117/p-o-"
strDPO2 = ".pdf"
'https://pdf.digitalpurchaseorder.com/22239/p-o-230018.pdf
'Supress error messages
On Error Resume Next
Set rng = Range("M:M").SpecialCells(xlCellTypeConstants, xlNumbers)
' get out if an error occurs, 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
' If c.Value < 230000 Then
If c.Value > 36000 And c.Value < 39999 Then
intHold = c.Value
c.Formula = "=HYPERLINK(""" & strURL & intHold & """, """ & intHold & """)"
Else
If c.Value > 230000 And c.Value < 24000 Then
intHold = c.Value
c.Formula = "=HYPERLINK(""" & strDPO23 & intHold & strDPO2 & """,""" & intHold & """)"
Else
If c.Value > 240000 And c.Value < 25000 Then
intHold = c.Value
c.Formula = "=HYPERLINK(""" & strDPO24 & intHold & strDPO2 & """,""" & intHold & """)"
End If
End If
Next c
Range("M:M").SpecialCells(xlCellTypeFormulas).Select
With Selection.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.Color = 16711680
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
' end of Hyperlink code
End Sub
Greg
