S
Shell
When I open a workbook (Excel 2000), I get the pop-up message that I have
external links. I cannot find the cell(s) that helped to produce this
message. However if I click on Edit, then Links, I find where the links are
going.
I need to find the cells that contain these links. I wrote some macros to
find formulas, but I get no hits. In the cases where I can find the cells,
the macros correctly lists these cells.
So, why can't I find these cells, and how do I find these cells.
I have included the code below:
Sub LastRow()
Dim LastRow As Long
Dim LastColumn As Long
Dim i As Integer
Dim objSht As Object
Open "C:\OnProcess\Special CRT Template Folder\FormulaList - " &
Mid(ActiveWorkbook.Name, 9, 5) & ".txt" For Output As #1
For i = 1 To Sheets.Count
Worksheets(i).Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.UsedRange.Column - 1 +
ActiveSheet.UsedRange.Columns.Count
Print #1, "Tab Name: " & ActiveSheet.Name & " " & "Last Row: " &
LastRow & " " & "Last Column: " & LastColumn
listFormula LastRow, LastColumn
Next i
Close #1
End Sub
Function listFormula(LastRow As Long, LastColumn As Long)
Dim r As Integer
Dim intC As Integer
Dim strC As String
Dim a As Integer
Dim x As String
For r = 1 To LastRow
For intC = 3 To LastColumn
Select Case intC
Case 3
strC = "C"
Case 4
strC = "D"
Case 5
strC = "E"
Case 6
strC = "F"
Case 7
strC = "G"
Case 8
strC = "H"
Case 9
strC = "I"
Case 10
strC = "J"
Case 11
strC = "K"
Case 12
strC = "L"
Case 13
strC = "M"
Case 14
strC = "N"
Case 15
strC = "O"
Case 16
strC = "P"
Case 17
strC = "Q"
Case 18
strC = "R"
Case 19
strC = "S"
Case 20
strC = "T"
Case 21
strC = "U"
Case 22
strC = "V"
Case 23
strC = "W"
Case 24
strC = "X"
Case 25
strC = "Y"
Case 26
strC = "Z"
End Select
'If InStr(1, Range(strC & r).Formula, "=") = 1 Then
x = Range(strC & r).Formula
If InStr(1, Range(strC & r).Formula, "C:\") > 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
Print #1, strC & r & " " & " " & Range(strC & r).Value
End If
If InStr(1, Range(strC & r).Formula, "R:\") > 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
If InStr(1, Range(strC & r).Formula, "#REF") > 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
'End If
Next intC
Next r
End Function
thanks
external links. I cannot find the cell(s) that helped to produce this
message. However if I click on Edit, then Links, I find where the links are
going.
I need to find the cells that contain these links. I wrote some macros to
find formulas, but I get no hits. In the cases where I can find the cells,
the macros correctly lists these cells.
So, why can't I find these cells, and how do I find these cells.
I have included the code below:
Sub LastRow()
Dim LastRow As Long
Dim LastColumn As Long
Dim i As Integer
Dim objSht As Object
Open "C:\OnProcess\Special CRT Template Folder\FormulaList - " &
Mid(ActiveWorkbook.Name, 9, 5) & ".txt" For Output As #1
For i = 1 To Sheets.Count
Worksheets(i).Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.UsedRange.Column - 1 +
ActiveSheet.UsedRange.Columns.Count
Print #1, "Tab Name: " & ActiveSheet.Name & " " & "Last Row: " &
LastRow & " " & "Last Column: " & LastColumn
listFormula LastRow, LastColumn
Next i
Close #1
End Sub
Function listFormula(LastRow As Long, LastColumn As Long)
Dim r As Integer
Dim intC As Integer
Dim strC As String
Dim a As Integer
Dim x As String
For r = 1 To LastRow
For intC = 3 To LastColumn
Select Case intC
Case 3
strC = "C"
Case 4
strC = "D"
Case 5
strC = "E"
Case 6
strC = "F"
Case 7
strC = "G"
Case 8
strC = "H"
Case 9
strC = "I"
Case 10
strC = "J"
Case 11
strC = "K"
Case 12
strC = "L"
Case 13
strC = "M"
Case 14
strC = "N"
Case 15
strC = "O"
Case 16
strC = "P"
Case 17
strC = "Q"
Case 18
strC = "R"
Case 19
strC = "S"
Case 20
strC = "T"
Case 21
strC = "U"
Case 22
strC = "V"
Case 23
strC = "W"
Case 24
strC = "X"
Case 25
strC = "Y"
Case 26
strC = "Z"
End Select
'If InStr(1, Range(strC & r).Formula, "=") = 1 Then
x = Range(strC & r).Formula
If InStr(1, Range(strC & r).Formula, "C:\") > 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
Print #1, strC & r & " " & " " & Range(strC & r).Value
End If
If InStr(1, Range(strC & r).Formula, "R:\") > 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
If InStr(1, Range(strC & r).Formula, "#REF") > 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
'End If
Next intC
Next r
End Function
thanks