Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Loops and counters
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Loops and counters
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" wrote in message ... 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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Loops and counters
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Loops and counters
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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Loops and counters
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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Loops and counters
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with Counters | Excel Discussion (Misc queries) | |||
Counters | Excel Programming | |||
Counters | Excel Worksheet Functions | |||
Using for next with two counters | Excel Programming | |||
Creating Loops | Excel Programming |