select all cells in an active sheet containg links to other (external) workbooks.

[al007]

How can i make the code below select all cells in an active sheet
containg links to other (external) workbooks.

For Each cell In ActiveSheet.UsedRange
If Left(cell.Formula, 2) = "=[" Then 'then select
cells how to put selection code??



Thxs

[Don Guillett]

The question back is why do you need to select? You can do almost anything
desired without ever making selections.

--
Don Guillett
SalesAid Software
[email protected]
"al007" <[email protected]> wrote in message
news:[email protected]...
> How can i make the code below select all cells in an active sheet
> containg links to other (external) workbooks.
>
> For Each cell In ActiveSheet.UsedRange
> If Left(cell.Formula, 2) = "=[" Then 'then select
> cells how to put selection code??
>
>
>
> Thxs
>

[Gary''s Student]

To select a set of cells that meet a criteria, let's build the range with
unions and then select the built range:


Sub selector()
Dim r As Range
Dim rr As Range
For Each r In ActiveSheet.UsedRange
If Left(r.Formula, 2) = "=[" Then
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Next
rr.Select
End Sub


Here we build rr one cell at a time and then do a single selection of all
the celss in it.
--
Gary''s Student


"al007" wrote:

> How can i make the code below select all cells in an active sheet
> containg links to other (external) workbooks.
>
> For Each cell In ActiveSheet.UsedRange
> If Left(cell.Formula, 2) = "=[" Then 'then select
> cells how to put selection code??
>
>
>
> Thxs
>
>

[Dave Peterson]

And remember that not all formulas that link back to other workbooks start with
=[.



al007 wrote:
>
> How can i make the code below select all cells in an active sheet
> containg links to other (external) workbooks.
>
> For Each cell In ActiveSheet.UsedRange
> If Left(cell.Formula, 2) = "=[" Then 'then select
> cells how to put selection code??
>
> Thxs

--

Dave Peterson

[al007]

you are perfectly right - how can what code would identify links to
workbooks which are closed?


i.e If Left(cell.Formula, 2) contains ":\" Then 'then select

thxs



Dave Peterson wrote:
> And remember that not all formulas that link back to other workbooks start with
> =[.
>
>
>
> al007 wrote:
> >
> > How can i make the code below select all cells in an active sheet
> > containg links to other (external) workbooks.
> >
> > For Each cell In ActiveSheet.UsedRange
> > If Left(cell.Formula, 2) = "=[" Then 'then select
> > cells how to put selection code??
> >
> > Thxs
>
> --
>
> Dave Peterson

[al007]

does not seem to work - can u help
Gary''s Student wrote:
> To select a set of cells that meet a criteria, let's build the range with
> unions and then select the built range:
>
>
> Sub selector()
> Dim r As Range
> Dim rr As Range
> For Each r In ActiveSheet.UsedRange
> If Left(r.Formula, 2) = "=[" Then
> If rr Is Nothing Then
> Set rr = r
> Else
> Set rr = Union(rr, r)
> End If
> End If
> Next
> rr.Select
> End Sub
>
>
> Here we build rr one cell at a time and then do a single selection of all
> the celss in it.
> --
> Gary''s Student
>
>
> "al007" wrote:
>
> > How can i make the code below select all cells in an active sheet
> > containg links to other (external) workbooks.
> >
> > For Each cell In ActiveSheet.UsedRange
> > If Left(cell.Formula, 2) = "=[" Then 'then select
> > cells how to put selection code??
> >
> >
> >
> > Thxs
> >
> >

[Dave Peterson]

looking for :\ is a problem on links to files that are on network shares:

\\server\share\folder\myfile.xls

You could look for [ or ] or .xls a combination of these.

But they could be embedded into strings, too. So it's not foolproof.

But you will want to limit yourself to just the formulas.

This may yield some "false positives" if you have formulas that contain .xls or
[ or ].

Option Explicit
Sub testme()

Dim myRng As Range
Dim myExtLinkRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myList As Variant
Dim iCtr As Long
Dim FirstAddress As String
Dim FoundCell As Range

Set wks = Worksheets("sheet1")

myList = Array(".xls", "[", "]")

Set myRng = Nothing
On Error Resume Next
Set myRng = wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas!"
Exit Sub
End If

With myRng
For iCtr = LBound(myList) To UBound(myList)
Set FoundCell = .Cells.Find(what:=myList(iCtr), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'do nothing, not found
Else
FirstAddress = FoundCell.Address
Do
If myExtLinkRng Is Nothing Then
Set myExtLinkRng = FoundCell
Else
If Intersect(myExtLinkRng, FoundCell) Is Nothing Then
Set myExtLinkRng = Union(myExtLinkRng, FoundCell)
Else
'already part of the range
End If
End If
Set FoundCell = .FindNext(FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If
Next iCtr
End With

If myExtLinkRng Is Nothing Then
MsgBox "I didn't find it"
Else
MsgBox "maybe it's part of the selected range"
Application.Goto myExtLinkRng
End If

End Sub


======
If I were really looking for links to other workbooks, I'd use Bill Manville's
FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

al007 wrote:
>
> you are perfectly right - how can what code would identify links to
> workbooks which are closed?
>
> i.e If Left(cell.Formula, 2) contains ":\" Then 'then select
>
> thxs
>
> Dave Peterson wrote:
> > And remember that not all formulas that link back to other workbooks start with
> > =[.
> >
> >
> >
> > al007 wrote:
> > >
> > > How can i make the code below select all cells in an active sheet
> > > containg links to other (external) workbooks.
> > >
> > > For Each cell In ActiveSheet.UsedRange
> > > If Left(cell.Formula, 2) = "=[" Then 'then select
> > > cells how to put selection code??
> > >
> > > Thxs
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson