Creating Loops and counters

  • Thread starter Thread starter JayLatimer
  • Start date Start date
J

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"
 
Try this in place of your current code...

For X = 2 to 47
Cells(X, "H").Hyperlinks(1).TextToDisplay = "Tax Record"
Next
 
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
 
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
 
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?
 
you would need to ask in an Access group

JayLatimer said:
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?
Click to expand...
 
Back
Top