+ Reply to Thread
Results 1 to 6 of 6

Reference row of Active Cell in Hyperlink formula

Hybrid View

  1. 08-12-2015, 01:07 PM #1
    delaing
    delaing is offline
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Reference row of Active Cell in Hyperlink formula

    I need to modify my existing code to reference the Row of the Active Cell.
    Then, use that Row reference in part of my formula.

    Sub formula_mcad_datasheetPDF()

' 8/12/15 - Inserts the hyperlink to open the PDF of the MCAD datasheet that
' PFBA has downloaded to my WO folder.

Dim mcadPDF As String

mcadPDF = "=HYPERLINK(""L:\PES\PES Work Orders\Delain WOs\MCAD cases\Completed\""&$C$5&"" (""&$C$4&"") 2014\""&C11&"".pdf.pdf"", ""MCAD Datasheet"")"
    ActiveCell.Formula = mcadPDF
    
End Sub
    The highlighted reference is where I would like to change 11 of C11 to the Row of the Active Cell.

    Thank you for your help,
    Delain
    Reply With QuoteRegister To Reply
  2. 08-12-2015, 01:15 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,696

    Re: Reference row of Active Cell in Hyperlink formula 

    Option Explicit

Sub formula_mcad_datasheetPDF()

' 8/12/15 - Inserts the hyperlink to open the PDF of the MCAD datasheet that
' PFBA has downloaded to my WO folder.

Dim mcadPDF As String

mcadPDF = "=HYPERLINK(""L:\PES\PES Work Orders\Delain WOs\MCAD cases\Completed\""&$C$5&"" (""&$C$4&"") 2014\""&C" & ActiveCell.Row & "&"".pdf.pdf"", ""MCAD Datasheet"")"
    ActiveCell.Formula = mcadPDF
    
End Sub

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 08-12-2015, 02:35 PM #3
    delaing
    delaing is offline
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Reference row of Active Cell in Hyperlink formula

    Excellent & sweet, at the same time!

    Thanks for the quick response.

    Delain
    Reply With QuoteRegister To Reply
  4. 08-12-2015, 02:39 PM #4
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Reference row of Active Cell in Hyperlink formula

    Another option, using R1C1
    Sub formula_mcad_datasheetPDF()

' 8/12/15 - Inserts the hyperlink to open the PDF of the MCAD datasheet that
' PFBA has downloaded to my WO folder.

   mcadPDF  = "=HYPERLINK(""L:\PES\PES Work Orders\Delain WOs\MCAD cases\Completed\""&R5C3&"" (""&R4C3&"") 2014\""&RC3&"".pdf.pdf"", ""MCAD Datasheet"")"
    ActiveCell.FormulaR1C1 =mcadPDF

End Sub
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  5. 08-12-2015, 04:46 PM #5
    delaing
    delaing is offline
    Forum Contributor delaing's Avatar
    Join Date
    07-16-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: Reference row of Active Cell in Hyperlink formula

    Norie, thanks for your input. Mr. Shuttleworth's solution is exactly what I was looking for.


    Delain
    Reply With QuoteRegister To Reply
  6. 08-12-2015, 02:45 PM #6
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,696

    Re: Reference row of Active Cell in Hyperlink formula

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. External cell reference based on hyperlink in a cell in the active sheet
    By Sydstar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2014, 08:25 AM
  2. [SOLVED] Formula/Macro to give active cell reference in cell
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-23-2014, 03:36 AM
  3. hyperlink formula for cell reference to folder address
    By B Love in forum Excel General
    Replies: 2
    Last Post: 07-14-2011, 03:16 PM
  4. How to change the cell reference in a LEN formula by using Active cells
    By crisshinn in forum Excel General
    Replies: 4
    Last Post: 02-22-2011, 10:56 AM
  5. How to change active cell after hyperlink clicked
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 12-28-2009, 02:29 PM
  6. Use Active Cell to create Hyperlink
    By cob943 in forum Excel General
    Replies: 2
    Last Post: 10-08-2008, 03:37 PM
  7. script to hyperlink and reference a cell value in the hyperlink
    By Natasha D. in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-17-2006, 02:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1