0

I have ColumnB in Excel populated with full pathnames. I want to create a Hyperlink with a [friendly_name] for each ColumnB pathname, in the same row in ColumnC. My hang-up is when reading ColumnB and Offsetting to ColumnC to save the Hyperlink with that cell's Range which is changing with each for/next iteration. I have tried to use a Variant as the ColumnC Range will be changing, but without success. Here is my code thus far. 

Sub AddHyperlinkFromPath()

Dim p As Integer 'Iteration number
Dim NumRowsP As Integer 'Row count
Dim myPath As String 'Pathname
Dim myName As String 'File name extracted from myPath

    Worksheets("AllFiles").Activate 'Source worksheet

    NumRowsP = Range("B8", Range("B8").End(xlDown)).Rows.Count + 7 'Row count
    Range("B8").Select 'First full pathname location
        For p = 8 To NumRowsP 'Begin at the 8th row to last row

            myPath = ActiveCell.Value 'Get pathname
                p = 1

                    myName = Mid(myPath, InStrRev(myPath, "\") + 1, 999) 'Get filename from pathname
            ActiveCell.Offset(0, 1).Select 'Moved to Column C

                    ActiveSheet.Hyperlinks.Add Anchor:=Range("c" & p), Address:=myPath, TextToDisplay:=myName
                p = p + 1 'Iterate to next row
            ActiveCell.Offset(0, -1).Select 'Move back to Column B

        Next

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
LawGuy
  • 65
  • 6
  • 1
    Start [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Jan 30 '20 at 21:26
  • 2
    And don't use `Integer` either, use `Long`. – BigBen Jan 30 '20 at 21:26
  • 1
    [Another good question to read](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Jan 30 '20 at 21:31

0 Answers0