Hyperlink Address and SubAddress not concatenating correctly
 

2003

The code below ******** does not generate the correct hyperlink
information.

The actual "Address" is:
\\OPC01\VOL1\PROD\PRIVATE\SARBANES\Loan Loss Reserve 20\Lender
Liability Reserve 20d\2006\Inventory_Reports_Spreadsheets\Review SOX
1st Stage 20d

The actual "SubAddress" (which is a filename) is:
20d contingent liability spreadsheet 8-15-2006.xls

If I do a Ctrl-K in i.e. cell AA9 after the code above and look at the
"Edit" address I see:

\\OPC01\VOL1\PROD\PRIVATE\SARBANES\Loan Loss Reserve 20\Lender
Liability Reserve 20d\2006\Inventory_Reports_Spreadsheets\Review SOX
1st Stage 20d#20d contingent liability spreadsheet 8-15-2006.xls

NOTE: The "#" between the Address and SubAddress (this # causes the
Hyperlink to fail)

If I attempt the insert a "\" I get either:
#\ or \# depending where I attempt to concatenate it


Sub Link()
'
' Code to generate a Hyperlink from information on the Activesheet
'
Dim MyCounter As Long
MyCounter = Selection.Cells.Row - 1
ActiveSheet.Range("AA:AA").Hyperlinks.Delete
For MyCounter = MyCounter + 1 To Cells.Rows.Count
With ActiveSheet
If UCase(.Cells(MyCounter, "H").Text) = "YES" Then
.Cells(MyCounter, "AA").Hyperlinks.Add Anchor:= _
.Cells(MyCounter, "AA"), Address:=.Cells(MyCounter, "A").Value _
SubAddress:=.Cells(MyCounter, "F").Value, TextToDisplay:="L"
' SubAddress:="\" & .Cells(MyCounter, "F").Value, _
' TextToDisplay:="L"
Else
Exit For
End If
End With
Next MyCounter
End Sub

If I manually create the link in cell AA9, I get:

\\Opc01\VOL1\PROD\PRIVATE\SARBANES\Loan Loss Reserve 20\Lender
Liability Reserve 20d\2006\Inventory_Reports_Spreadsheets\Review SOX
1st Stage 20d\20d contingent liability spreadsheet 8-15-2006.xls

Therefore, using VBA I get a "#" inserted after the Address which
causes the link to fail.

1) Why does this happen? (String limit?)
2) How to stop or workaround so that I can generate valid Hyperlinks
with VBA?

Thanks

EagleOne