"next without for" macro error

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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