Hi

This macro assume hyperlinks always start in row 2 and find the column with
hyperlinks and change the text in all hyperlinks:

Sub ChangelinkT()
Dim Col As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim r As Long
Application.ScreenUpdating = False

' Find Hyperlink column
FirstRow = 2
On Error Resume Next
For c = 1 To Columns.Count
a = Cells(2, c).Hyperlinks(1).Address
If Err.Number = 0 Then
Col = c
Exit For
End If
Err.Clear
Next
LastRow = Cells(Rows.Count, Col).End(xlUp).Row

For r = FirstRow To LastRow
Cells(r, Col).Hyperlinks(1).TextToDisplay = "Tax Record"
Next
Application.ScreenUpdating = True
End Sub

Regards,
Per

"JayLatimer" skrev i meddelelsen
...
I need a little programming encouragement€¦ I have created a macro in excel
that I need to loop and go through a range of cells within a column and
perform a function (Changing the displayed URL to the words Tax Record). I
can get it to work it but I have a line of VB for every line I want to
change. I think I need a loop and counter but I get lost every time I try
to
write these functions.

Here is the Marco code that I have in excel. This will change the url link
displayed to the word Tax Record as the url. Right now this will work for
column H, rows 2 through 47. I have multiple spread sheets that have
different columns that have the URL and up to several hundred rows€¦.I to
be
able to easily change the column as so that I can use it for multiple
spreadsheets and the loop needs to exit when it comes to a blank cell.

Thanks in advance for any help you can give me on this€¦.



Sub ChangelinkT()
'
' ChangelinkT Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("H2").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H3").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H4").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H5").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H6").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H7").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H8").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H9").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H10").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H11").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H12").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H13").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H14").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H15").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H16").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H17").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H18").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H19").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H20").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H21").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H22").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H23").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H23").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H24").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H25").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H26").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H27").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H28").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H29").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H30").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H31").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H32").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H33").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H34").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H35").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H36").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H37").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H38").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H39").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H40").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H41").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H42").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H43").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H44").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H45").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H46").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"
Range("H47").Select
Selection.Hyperlinks(1).TextToDisplay = "Tax Record"