Custom Link Function

astephanov

New Member
Joined
Apr 4, 2009
Messages
19
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.

Code:
[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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

astephanov

New Member
Joined
Apr 4, 2009
Messages
19
Pls ignore the above. I sort of figured it out. I will create a new post with a question I have.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,978
Messages
5,621,942
Members
415,868
Latest member
Pita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top