PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Creating Loops and counters

Reply
Thread Tools Rate Thread

Creating Loops and counters

 
 
JayLatimer
Guest
Posts: n/a
 
      30th Oct 2009
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"
 
Reply With Quote
 
 
 
 
Rick Rothstein
Guest
Posts: n/a
 
      30th Oct 2009
Try this in place of your current code...

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

--
Rick (MVP - Excel)


"JayLatimer" <(E-Mail Removed)> wrote in message
news:C19B88BD-E0C8-4AE8-A439-(E-Mail Removed)...
>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"
>
 
Reply With Quote
 
Per Jessen
Guest
Posts: n/a
 
      30th Oct 2009
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" <(E-Mail Removed)> skrev i meddelelsen
news:C19B88BD-E0C8-4AE8-A439-(E-Mail Removed)...
>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"
>
 
Reply With Quote
 
Mike H
Guest
Posts: n/a
 
      30th Oct 2009
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"
>
 
Reply With Quote
 
JayLatimer
Guest
Posts: n/a
 
      30th Oct 2009
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" wrote:

> 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"
> >
 
Reply With Quote
 
Mike H
Guest
Posts: n/a
 
      30th Oct 2009
you would need to ask in an Access group

"JayLatimer" wrote:

> 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" wrote:
>
> > 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"
> > >
 
Reply With Quote
 
 
 
Reply

« Organize the worksheets in numerical order | Copied Pivot Tables with same data source »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating Custom Hit Counters =?Utf-8?B?RGFsdWNo?= Microsoft Frontpage 1 14th Feb 2006 03:15 AM
Creating Performance Counters Adam G. Microsoft Dot NET Framework 0 1st Nov 2004 08:11 PM
initialising and creating objects in loops ajit goel Microsoft Dot NET 1 17th Sep 2004 06:26 AM
Creating a "Movie" that loops =?Utf-8?B?RGViYnkgRG9lcyBNZWRpYQ==?= Windows XP MovieMaker 4 26th Jun 2004 04:54 PM
Re: Creating Loops Frank Kabel Microsoft Excel Programming 1 5th May 2004 07:26 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 08:59 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top