Sleeper: Search cells for no Hyperlink

[GROLEAUD]

I am a payroll end user with very little knowledge of VBAhttp://vbaexpress.com/forum/images/smilies/100.gif I spent several hours on the intranet last night searching but was unsuccessful, other than finding this site.http://vbaexpress.com/forum/images/smilies/banghead.gif

I need to be able to locate all cells as described below that do not have a hyperlink attached. I would like a macro to run that would take me to each one either upon opening the file or closing the file (Or having a hot key)

Search selected worksheets in Excel (2000) (A thru J) and (Q thru Z), looking at rows 16-38 in column A. I have some sheets that I dont want to search but I believe that I can?t move the spreadsheet due to other calculations. There are approx 200 sheets.

Any help you could provide is greatly appreciated (as my IS support staff was cut).

http://vbaexpress.com/forum/images/smilies/102.gif

[Jacob Hilderbrand]

Try this as an example. This will show a message box with all the cell addresses that you want.



Option Explicit

Sub Macro1()
Dim SheetCol As Collection
Dim ws As Worksheet
Dim CheckRange As Range
Dim h As Hyperlink
Dim i As Long
Dim Cel As Range
Dim Msg As String
Set SheetCol = New Collection
'Add sheet names here
SheetCol.Add "sheet1"
SheetCol.Add "sheet2"
SheetCol.Add "sheet3"
For i = 1 To SheetCol.Count
'Verify the sheet exists
On Error Resume Next
Set ws = Sheets(SheetCol(i))
On Error GoTo 0
If ws Is Nothing Then
GoTo NextLoop:
End If
'Verify there is a range of cells to check
On Error Resume Next
Set CheckRange = ws.Range("A:J,Q:Z").SpecialCells(xlCellTypeConstants, 23)
On Error GoTo 0
If CheckRange Is Nothing Then
GoTo NextLoop:
End If
'Loop through all cells in the range and test for a hyperlink
For Each Cel In CheckRange
'Test for a hyperlink
On Error Resume Next
Set h = Cel.Hyperlinks(1)
On Error GoTo 0
'If there is no hyperlink, add that cell to the final range
If h Is Nothing Then
Msg = Msg & vbNewLine & ws.Name & "!" & Cel.Address(False, False)
End If
Set h = Nothing
Next
NextLoop:
Set h = Nothing
Set CheckRange = Nothing
Set ws = Nothing
Next i
If Msg <> "" Then
MsgBox Msg, vbInformation, "No Hyperlink Addresses"
End If
Set h = Nothing
Set Cel = Nothing
Set CheckRange = Nothing
Set ws = Nothing
Set SheetCol = Nothing
End Sub

[MWE]

I am a payroll end user with very little knowledge of VBAhttp://vbaexpress.com/forum/images/smilies/100.gif I spent several hours on the intranet last night searchign but was unsuccessful, other then finding this site.http://vbaexpress.com/forum/images/smilies/banghead.gif

I need to be able to locate all cells as described below that do not have a hyperlink attached. I would like a macro to run that would take me to each one either upon opening the file or closing the file (Or having a hot key)

Search selected worksheets in Excel (2000) (A thru J) and (Q thru Z), looking at rows 16-38 in column A. I have some sheets that I dont want to search but I believe that I can?t move the spreadsheet due to other calculations. There are approx 200 sheets.

Any help you could provide is greatly appreciated (as my IS support staff was cut).

http://vbaexpress.com/forum/images/smilies/102.gif
welcome to the forum

What you ask is not very hard to do, but you have not fully specified the problem. Please clarify:




how does the code know what sheets to examine? Is there a specific list? Given 200 sheets, the list might be pretty long. Maybe the list is sheets not to be examined. Would it make sense to have another tab that contained the names of the sheets to be examined (or not, whichever is shorter)? That makes the code much more generic. BTW, populating a new sheet with the names of all the current sheets is a very simple VBA procedure.
You state, "Search selected worksheets in Excel (2000) (A thru J) and (Q thru Z), looking at rows 16-38 in column A.". What about other columns, i.e., B to J and Q to Z. If the ranges to be examined are different sheet to sheet, perhaps the special tab mentioned in #1 could identify the ranges to be examined for each target sheet


You state, "... would take me to each one ...". I assume that you want the procedure to highlight the offending cell and then let you do something. Correct? If the used range of cells on any sheet is not very large (essentially all used cells on a given sheet fit in the visible window), you may want the procedure to highlight all offending cells on a sheet (change their color or something) and then stop to allow you to do what you want with those cells.

Having the procedure execute on startup, or on exit or via a "button" (or any combination): all pretty easy. I suspect that the simpliest implementation would be some combination.

[GROLEAUD]

Jake,

Thanks for the quick response. I want to make sure I understand how to add this. I opened the spreasheet and visual basic. I clicked on the tab that says VBA(project then filename). Do I then add this as a module. (This is what I thought I was supposed to do). I then changed the WS.range to my sheet names and saved. I tired to run and it doesn't do anything. What am I missing?