Hi,

This asks for column letter to work on and worksheet name.

Sub ChangelinkT()
Dim MyCol As String
Dim MySht As String
On Error Resume Next
MyCol = InputBox("Enter column LETTER(S)")
MySht = InputBox("Enter sheet name")
Lastrow = Sheets(MySht).Cells(Cells.Rows.Count, MyCol).End(xlUp).Row
For Each c In Sheets(MySht).Range(MyCol & "2:" & MyCol & Lastrow)
c.Hyperlinks(1).TextToDisplay = "Tax record"
Next
End Sub

Mike

"JayLatimer" wrote:

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"