Creating Loops and counters

[JayLatimer]

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"

 

[Rick Rothstein]

Try this in place of your current code...

For X = 2 to 47
Cells(X, "H").Hyperlinks(1).TextToDisplay = "Tax Record"
Next

 

[Per Jessen]

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

 

[Mike H]

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]

Thank you very much for your help. If I wanted to put this into Access also
would I need to change the Sheets to Tables?

 

[Mike H]

you would need to ask in an Access group

Thank you very much for your help. If I wanted to put this into Access also
would I need to change the Sheets to Tables?