Custom Link Function

[astephanov]

My company generates the following types of links:

1. [FONT=&quot]http://websrv2/~clearweb/xfer_incoming/cme/f549.cmerpt.rthcycle.20090403[/FONT]
2. [FONT=&quot][FONT=&quot]http://websrv2/~clearweb/thpclr_report/20090403/backoffice/BBVA.OPTFUT.customer.PositionCheck.20090403[/FONT][/FONT]

The only thing changes daily is the date

I want to create a custom function that would prompt the user to paste in a link, point to formatted date and give the link a name. All that the user would have to do afterwords is to update the formatted link.

[FONT=&quot]Function Format_Date(YrDate As Date)[/FONT] 
[FONT=&quot]Format_Date = Format(YrDate, "yyyymmdd")[/FONT] 
[FONT=&quot]End Function[/FONT]
[FONT=&quot]
[/FONT] 
  [FONT=&quot]Function IBGLink(URL As String, Formatted_Date As String, Name As String)[/FONT] 
[FONT=&quot]Dim A As String[/FONT] 
[FONT=&quot]    A = Mid(URL, Worksheet.Function.Find("/200", URL), 8)[/FONT] 
[FONT=&quot]Dim B As String[/FONT] 
[FONT=&quot]    B = Left(URL, Worksheet.Function.Find("/200", URL))[/FONT] 
[FONT=&quot]Dim C As String[/FONT] 
[FONT=&quot]    C = Right(URL, Len(URL) - (Len(A) + Len(B)))[/FONT] 
[FONT=&quot]Dim D As String[/FONT] 
[FONT=&quot]    D = Left(C, Worksheet.Function.Find(".200", C))[/FONT] 
[FONT=&quot]    If Worksheet.Function.IsErr(A) Then[/FONT] 
[FONT=&quot]    IBGLink = Worksheet.Function.Hyperlink(D & Formatted_Date)[/FONT] 
[FONT=&quot]    Else: IBGLink = Worksheet.Function.Hyperlink(B & Formatted_Date & D & Formatted_Date)[/FONT] 
[FONT=&quot]    End If[/FONT] 
[FONT=&quot]End Function[/FONT]

I have a problem with the second function. I tried building the parsing logic (the parsing logic does work on its own) into the IF Then statement, but it get too complicated and I run into problems with the Call function. I thought the above would work, but I does not for some reason. It would be great if both functions could be integrated somehow.

Thank you for help. I really need it.

 

[astephanov]

Pls ignore the above. I sort of figured it out. I will create a new post with a question I have.