I'm trying to look through some workbooks to find what cells have a
particular cell ame in the formulae, but it debugs on doeesn't support the
method in lookin=xlFormulas or using the constant -4123.
the code is
With ActiveWorkbook
Set C = .Find(Tsh.Range("C" & cnt).Value, -4123)
If Not C Is Nothing Then
firstAddress = C.Address
Do
If Not Err.Number = 91 Then: Tsh.Range(cl &
cnt).Value = "Y": Exit Do
If Err.Number = 91 Then: Err.Clear
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <
firstAddress
End If
End With

Could someone help with this one please? Regards, Brett

You need to look at a range object not a workbook object. You are missing
the sheets and Cells toindicate the entire worksheet.

from
Set C = .Find(Tsh.Range("C" & cnt).Value, -4123)

to
set Sht = .Sheets("Sheet1")
Set C = Sht.Cells.Find(what:=Tsh.Range("C" & cnt).Value, _
lookin:=xlvalues,lookat:=xlwhole)

I assume the -4123 is the value in Tsh.Range("C" & cnt).Value

"Brettjg" wrote:

I'm trying to look through some workbooks to find what cells have a
particular cell ame in the formulae, but it debugs on doeesn't support the
method in lookin=xlFormulas or using the constant -4123.
the code is
With ActiveWorkbook
Set C = .Find(Tsh.Range("C" & cnt).Value, -4123)
If Not C Is Nothing Then
firstAddress = C.Address
Do
If Not Err.Number = 91 Then: Tsh.Range(cl &
cnt).Value = "Y": Exit Do
If Err.Number = 91 Then: Err.Clear
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <
firstAddress
End If
End With

Could someone help with this one please? Regards, Brett

also -

if you don't name the optional arguments you need to put them in the right
order
change
Find(Tsh.Range("C" & cnt).Value, -4123)
to
Find(Tsh.Range("C" & cnt).Value, , -4123)
or
Find(Tsh.Range("C" & cnt).Value, ,xlFormulas)
or
Find(Tsh.Range("C" & cnt).Value, LookIn:=xlFormulas)

I don;'t follow what that err = 91 is all about

Regards,
Peter T



"joel" wrote in message
...
You need to look at a range object not a workbook object. You are missing
the sheets and Cells toindicate the entire worksheet.

from
Set C = .Find(Tsh.Range("C" & cnt).Value, -4123)

to
set Sht = .Sheets("Sheet1")
Set C = Sht.Cells.Find(what:=Tsh.Range("C" & cnt).Value, _
lookin:=xlvalues,lookat:=xlwhole)

I assume the -4123 is the value in Tsh.Range("C" & cnt).Value

"Brettjg" wrote:

I'm trying to look through some workbooks to find what cells have a
particular cell ame in the formulae, but it debugs on doeesn't support
the
method in lookin=xlFormulas or using the constant -4123.
the code is
With ActiveWorkbook
Set C = .Find(Tsh.Range("C" & cnt).Value, -4123)
If Not C Is Nothing Then
firstAddress = C.Address
Do
If Not Err.Number = 91 Then: Tsh.Range(cl &
cnt).Value = "Y": Exit Do
If Err.Number = 91 Then: Err.Clear
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <
firstAddress
End If
End With

Could someone help with this one please? Regards, Brett

Hi Joel (again!). I was trying to emulate what you can do within Excel by
looking at the whole book. Maybe it can't be done from VB so I'll do it sheet
by sheet. Cheers, Brett

"joel" wrote:

You need to look at a range object not a workbook object. You are missing
the sheets and Cells toindicate the entire worksheet.

from
Set C = .Find(Tsh.Range("C" & cnt).Value, -4123)

to
set Sht = .Sheets("Sheet1")
Set C = Sht.Cells.Find(what:=Tsh.Range("C" & cnt).Value, _
lookin:=xlvalues,lookat:=xlwhole)

I assume the -4123 is the value in Tsh.Range("C" & cnt).Value

"Brettjg" wrote:

I'm trying to look through some workbooks to find what cells have a
particular cell ame in the formulae, but it debugs on doeesn't support the
method in lookin=xlFormulas or using the constant -4123.
the code is
With ActiveWorkbook
Set C = .Find(Tsh.Range("C" & cnt).Value, -4123)
If Not C Is Nothing Then
firstAddress = C.Address
Do
If Not Err.Number = 91 Then: Tsh.Range(cl &
cnt).Value = "Y": Exit Do
If Err.Number = 91 Then: Err.Clear
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <
firstAddress
End If
End With

Could someone help with this one please? Regards, Brett

Hi Peter, thanks for that. I probably don'y need the error check (in other
finds if it can't find what you're looking for you get an error 91). Regards,
Brett

"Peter T" wrote:

also -

if you don't name the optional arguments you need to put them in the right
order
change
Find(Tsh.Range("C" & cnt).Value, -4123)
to
Find(Tsh.Range("C" & cnt).Value, , -4123)
or
Find(Tsh.Range("C" & cnt).Value, ,xlFormulas)
or
Find(Tsh.Range("C" & cnt).Value, LookIn:=xlFormulas)

I don;'t follow what that err = 91 is all about

Regards,
Peter T



"joel" wrote in message
...
You need to look at a range object not a workbook object. You are missing
the sheets and Cells toindicate the entire worksheet.

from
Set C = .Find(Tsh.Range("C" & cnt).Value, -4123)

to
set Sht = .Sheets("Sheet1")
Set C = Sht.Cells.Find(what:=Tsh.Range("C" & cnt).Value, _
lookin:=xlvalues,lookat:=xlwhole)

I assume the -4123 is the value in Tsh.Range("C" & cnt).Value

"Brettjg" wrote:

I'm trying to look through some workbooks to find what cells have a
particular cell ame in the formulae, but it debugs on doeesn't support
the
method in lookin=xlFormulas or using the constant -4123.
the code is
With ActiveWorkbook
Set C = .Find(Tsh.Range("C" & cnt).Value, -4123)
If Not C Is Nothing Then
firstAddress = C.Address
Do
If Not Err.Number = 91 Then: Tsh.Range(cl &
cnt).Value = "Y": Exit Do
If Err.Number = 91 Then: Err.Clear
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <
firstAddress
End If
End With

Could someone help with this one please? Regards, Brett