VBA Code:
' ======================================================
' Begin code to Hyperlink process on Overview Worksheet.
' ======================================================
' Replaced message box with Max Function used on Column G (Group Number) on the Overview Worksheet.
xNumber = Application.WorksheetFunction.Max(Worksheets("overview").Range("g:g"))
j = 2 ' Sets j to starting value. Outside of the For Loop to continue down the column.
For start = 1 To xNumber ' Start large outside For Loop.
' Sets starting point for copy/paste of data to correct Cell.
Set rFoundCell = Worksheets("overview").Range("G1")
' Starts for loop to search for 'start' vaule on the Overview Worksheet in the Group column G.
For lCount = 1 To Application.WorksheetFunction.CountIf(Worksheets("overview").Range("g1:g200"), start)
' Finds the 'start' value on the Overview Worksheet in the Group column.
Set rFoundCell = Worksheets("overview").Columns(7).Find(what:=start, after:=rFoundCell, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
' Start of the Hyperlinking Group # to correct Group Worksheet.
' .Cells([row], [column]). .Cells(15, j) moves down to Row 15.
With rFoundCell
' Group column -- .Cells([row], [column]). .Cells(j, 6). not using offset.
Sheets("overview").Hyperlinks.Add Anchor:=Sheets("overview").Cells(j, 7), Address:="", SubAddress:="'Group " & start & "'!A1", TextToDisplay:=""
' If Not IsEmpty used to prevent With loop from crashing on a blank cell for Position, VG IP Address, Vault. <rlb 28 Feb. 2019>
If Not IsEmpty(Sheets("overview").Cells(j, 2)) Then ' If Position is empty skip to next Hyperlink creation.
Sheets("overview").Hyperlinks.Add Anchor:=Sheets("overview").Cells(j, 8), Address:="", SubAddress:="'Group " & start & "'!A1", TextToDisplay:=""
End If
If Not IsEmpty(Sheets("overview").Cells(j, 4)) Then ' If Vault is empty skip to next Hyperlink creation. <rlb 28 Feb. 2019>
Sheets("overview").Hyperlinks.Add Anchor:=Sheets("overview").Cells(j, 12), Address:="", SubAddress:="'Group " & start & "'!A1", TextToDisplay:=Cells(j, 12).Value
End If
If Not IsEmpty(Sheets("overview").Cells(j, 3)) Then ' If VG IP Address is empty skip to next Hyperlink creation. <rlb 28 Feb. 2019>
Sheets("overview").Hyperlinks.Add Anchor:=Sheets("overview").Cells(j, 10), Address:="http://" + Cells(j, 9).Value, SubAddress:="", TextToDisplay:=Cells(j, 10).Value
End If
End With
j = j + 1 ' update outside of the With loop, but inside the For loop. This moves down 1 cell each loop.
Next lCount
Next start
This code used to work, but now it is only linking the internals and only in row 2. So the Group, Position & Vault are hyperlinked but only for the first row (row 2, row 1 is the column header no need to link that). The IP Address is not linking, nor are any of the following rows.
Thank you in advance for help troubleshooting and correcting my code.