CODEWITHSUNDEEP
vbaexcel
DDEX
DDEX

Reputation: 455

How to create proper loops in VB (macro)

I have recorded and polished the following macro which should create an extra sheet with hypertext links pointing on a starting cell of each table within the original sheet called "All_tables". In this sheet, every single table is separated by a hash symbol (#). See a screenshot:

Sub Create_list_of_tables()

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "list of tables"

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "All_Tables!A22", TextToDisplay:="some variable pointing at the table name"
Range("A2").Select
End Sub

Now I would like to put it into a loop which would repeat itself ten (or more) times. I tried to use the hash symbol as a reference point for a program to find out at which cell he should point the hyperlink. Here is the result:

Sub Create_list_of_tables()    
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "list of tables"

Const cStrDivider As String = "#"

Dim rMyCell As Range
Dim table_number As Long
table_number = 0


Do Until table_number = 10
Set rMyCell = Range("cStrDivider").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "All_Tables!&rMyCell", TextToDisplay:="some variable pointing at the table name"
ActiveCell.Offset(1, 0).Select
table_number = table_number + 1
Loop

End Sub

And it doesn't work. I am totally new to macro and VB programming so I'd be really happy if you could at least show me the direction. Is my approach is completely wrong?

Thank you so much

Upvotes: 0

Views: 719

Answers (1)

Brad
Brad

Reputation: 12245

I'm not sure exactly where you want your hyperlink pointing but this should get you a good start. Things to look out for:

  • Don't use Select or Selection statements. They are slow and can produce undesirable effects. Instead use very explicit statements that do not depend on cursor position but rater the absolutle position of where you know things are.
  • Use the Find and FindNext method of a range object to locate strings. When FindNext can't find anything more it returns nothing. Good to check for instead of doing your table_number loop.

updated

Sub Create_list_of_tables()

    Const cStrDivider As String = "#"

    Dim sht As Worksheet, rMyCell As Range, rSearchRange As Range
    Dim testSht As Worksheet, firstMyCell As Range

    Set sht = ActiveSheet

    On Error Resume Next
    Set testSht = ActiveWorkbook.Sheets("All_Tables")
    If Err.Number <> 9 Then
        Application.DisplayAlerts = False
        testSht.Delete
        Application.DisplayAlerts = True    'important to set back to true!
    End If
    On Error GoTo 0

    ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
    ActiveWorkbook.Sheets(Sheets.Count).Name = "All_Tables"

    Set rSearchRange = sht.Range("A:A")

    'do initial "Find"
    Set rMyCell = rSearchRange.Find(cStrDivider)
    Set firstMyCell = rMyCell

    Do
        sht.Hyperlinks.Add Anchor:=rMyCell.Offset(0, 1), Address:="All_Tables!" & rMyCell.Address, _
            TextToDisplay:="Link"

        'get the next "MyCell" to use from the master range to search
        Set rMyCell = rSearchRange.FindNext(rMyCell)
        'increment your table counter (if you want to do this you can still
        table_number = table_number + 1
        Debug.Print firstMyCell.Address
        Debug.Print rMyCell.Address
    Loop While firstMyCell.Address <> rMyCell.Address

End Sub

See how that works an move on from there.

Upvotes: 1

Related Questions