I am trying to make hyperlinks from list of addresses. I am using this
macro and am getting a "next without for" error at the end. Can someone tell
me what I need to do to fix this? Thanks,

Todd


Sub MakeHyperlinkFormulas()
Dim cell As Range
Dim hyperlinkaddress As String, hyperlinktext As String
For Each cell In Selection
hyperlinkaddress = Trim(cell.Text)
hyperlinktext = Trim(cell.Text)
If hyperlinktext = "" Then GoTo skipit
If hyperlinktext < "" Then
If InStr(1, hyperlinkaddress, "@") Then
If LCase(Left(hyperlinkaddress, 7)) < "mailto:" Then
hyperlinkaddress = "mailto:" & hyperlinkaddress
End If
Else
If InStr(1, hyperlinkaddress, ".") = 0 Then GoTo skipit
If LCase(Left(hyperlinkaddress, 7)) < "http://" Then
hyperlinkaddress = "http://" & hyperlinkaddress
End If
End If
cell.Formula = "=HYPERLINK(""" & hyperlinkaddress & _
""",""" & hyperlinktext & """)"
skipit:
Next cell
End Sub

Hi Todd,

You don't need this line:
If hyperlinktext = "" Then GoTo skipit
because your code only procedes if your variable is not empty. Subsequently,
the label "skipit:" can go! Try the following..

Revised code:
Sub MakeHyperlinkFormulas()
Dim cell As Range
Dim hyperlinkaddress As String, hyperlinktext As String
For Each cell In Selection
hyperlinkaddress = Trim(cell.Text)
hyperlinktext = Trim(cell.Text)
If hyperlinktext < "" Then
If InStr(1, hyperlinkaddress, "@") Then
If LCase(Left(hyperlinkaddress, 7)) < "mailto:" Then
hyperlinkaddress = "mailto:" & hyperlinkaddress
End If
Else
If InStr(1, hyperlinkaddress, ".") 0 Then 'if it's there
If LCase(Left(hyperlinkaddress, 7)) < "http://" Then
hyperlinkaddress = "http://" & hyperlinkaddress
End If
End If
End If
cell.Formula = "=HYPERLINK(""" & hyperlinkaddress & _
""",""" & hyperlinktext & """)"
Next cell
End Sub

correction: my post is missing the last "End If". It's the same one yours is
missing <BTW, which is why you're getting the error; -your still in the "If"
block and trying to execute "Next" which has no "For" within the "If" block.

With the code below, if hyperlinktext = "" then it skips everything and
executes the "Next" statement.

Sub MakeHyperlinkFormulas()
Dim cell As Range
Dim hyperlinkaddress As String, hyperlinktext As String
|--For Each cell In Selection
| hyperlinkaddress = Trim(cell.Text)
| hyperlinktext = Trim(cell.Text)
| |--If hyperlinktext < "" Then
| | |--If InStr(1, hyperlinkaddress, "@") Then
| | | |--If LCase(Left(hyperlinkaddress, 7)) < "mailto:" Then
| | | | hyperlinkaddress = "mailto:" & hyperlinkaddress
| | | |--End If
| | |--Else
| | | |--If InStr(1, hyperlinkaddress, ".") 0 Then 'if it's there
| | | | |--If LCase(Left(hyperlinkaddress, 7)) < "http://" Then
| | | | | hyperlinkaddress = "http://" & hyperlinkaddress
| | | | |--End If
| | | |--End If
| | |--End If
| | cell.Formula = "=HYPERLINK(""" & hyperlinkaddress & _
| | """,""" & hyperlinktext & """)"
| |--End If
|--Next cell
End Sub

Sorry about that! ..better get some more coffee!
Regards,
GS

Do your IF ... END IFs balance up because that can sometimes showup as the
kind of error you are experiencing

"Todd" wrote:

I am trying to make hyperlinks from list of addresses. I am using this
macro and am getting a "next without for" error at the end. Can someone tell
me what I need to do to fix this? Thanks,

Todd


Sub MakeHyperlinkFormulas()
Dim cell As Range
Dim hyperlinkaddress As String, hyperlinktext As String
For Each cell In Selection
hyperlinkaddress = Trim(cell.Text)
hyperlinktext = Trim(cell.Text)
If hyperlinktext = "" Then GoTo skipit
If hyperlinktext < "" Then
If InStr(1, hyperlinkaddress, "@") Then
If LCase(Left(hyperlinkaddress, 7)) < "mailto:" Then
hyperlinkaddress = "mailto:" & hyperlinkaddress
End If
Else
If InStr(1, hyperlinkaddress, ".") = 0 Then GoTo skipit
If LCase(Left(hyperlinkaddress, 7)) < "http://" Then
hyperlinkaddress = "http://" & hyperlinkaddress
End If
End If
cell.Formula = "=HYPERLINK(""" & hyperlinkaddress & _
""",""" & hyperlinktext & """)"
skipit:
Next cell
End Sub