Hi All,
The function below resides in an Add-In.
It runs as part of a macro executed at the open event.

The open event code is: Call Wbk_Open(Status)
The Wbk_Open sub is also in the addin and uses the function.

All is well when the wbk opened after the personal.xls is NOT read only.
All is well when the read only wbk is opened after a 'regular' wbk.
I get the 1004 run time error at the set line noted below when the 1st wbk
opened after the personal is a backup wbk opened as read only.

I've stepped thru the code and all variables seem to have good values.
MsoMaxRow is a public constant in Declarations in the AddIn.
The read only wbk and the one from which is was created both reference the
Add IN.

I don't know where to go from here.
Thanks.


Function zFind_Row1Cf(Ws As Worksheet, sLookFor As String, _
Col As Integer, FmRow As Long, ToRow As Long, _
bXlWhole As Boolean) As Long
'Return the row of the cell where a string value is found in one column.
'Zero returned if not found. bXlwhole= true= string occupies entire cell.
Dim It As Range, WhoOrPrt

If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart
If FmRow < 1 Then FmRow = 1
If ToRow < 1 Or ToRow MSoMaxRow Then ToRow = MSoMaxRow

'the set gets the run time error and shows 'yellow'
Set It = Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

If Not It Is Nothing Then zFind_Row1Cf = It.Row
End Function
--
Neal Z

This line:

Set It = Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

has unqualified range references: cells() will belong to the activesheet if the
code is in a general module. Or it will refer to the sheet owning the code if
it's behind a worksheet module.

You could use:

Set It = Ws.Range(ws.Cells(FmRow, Col), ws.Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

to make sure that those cells() belong to the Ws worksheet.

Or to save typing (and easier to copy|paste to other procedures):

with ws
Set It = .Range(.Cells(FmRow, Col), .Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)
end with

All those .range(), .cells() will refer to the object in the previous with
statement. In this case, Ws.

Neal Zimm wrote:

Hi All,
The function below resides in an Add-In.
It runs as part of a macro executed at the open event.

The open event code is: Call Wbk_Open(Status)
The Wbk_Open sub is also in the addin and uses the function.

All is well when the wbk opened after the personal.xls is NOT read only.
All is well when the read only wbk is opened after a 'regular' wbk.
I get the 1004 run time error at the set line noted below when the 1st wbk
opened after the personal is a backup wbk opened as read only.

I've stepped thru the code and all variables seem to have good values.
MsoMaxRow is a public constant in Declarations in the AddIn.
The read only wbk and the one from which is was created both reference the
Add IN.

I don't know where to go from here.
Thanks.

Function zFind_Row1Cf(Ws As Worksheet, sLookFor As String, _
Col As Integer, FmRow As Long, ToRow As Long, _
bXlWhole As Boolean) As Long
'Return the row of the cell where a string value is found in one column.
'Zero returned if not found. bXlwhole= true= string occupies entire cell.
Dim It As Range, WhoOrPrt

If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart
If FmRow < 1 Then FmRow = 1
If ToRow < 1 Or ToRow MSoMaxRow Then ToRow = MSoMaxRow

'the set gets the run time error and shows 'yellow'
Set It = Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

If Not It Is Nothing Then zFind_Row1Cf = It.Row
End Function
--
Neal Z

--

Dave Peterson

Dear Dave,
Thanks, it worked, but i don't understand why. Please bear with me for
minute. The devil of it is that I HAD the 'With Ws' construct. I read other
postings that said unless you have repeated refs to the With object, there
wasn't much efficiency gained so I changed the code to that which failed.

Seems to me that if I have:
Set It = Ws.range(cells(f,c),cells(t,c)).Find(whatever)

1) Here's the biggie: Even if the specified cells are 'pointing' to the
wrong sheet, WHY do I get the run time error rather than an incorrect result
of the find since I might be looking at data on a worksheet that's not the
one indicated by the Ws object ?

2) I'm kinda OK in accepting that the cells may not be in the Ws but
............ Ws.range(Ws.cells(etc),Ws.cells(etc)) "seems" redundant. That
which is inside the parenthesis of Ws.range "should" belong to Ws.range. Any
thoughts ?

thanks again,
Neal

--
Neal Z


"Dave Peterson" wrote:

This line:

Set It = Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

has unqualified range references: cells() will belong to the activesheet if the
code is in a general module. Or it will refer to the sheet owning the code if
it's behind a worksheet module.

You could use:

Set It = Ws.Range(ws.Cells(FmRow, Col), ws.Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

to make sure that those cells() belong to the Ws worksheet.

Or to save typing (and easier to copy|paste to other procedures):

with ws
Set It = .Range(.Cells(FmRow, Col), .Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)
end with

All those .range(), .cells() will refer to the object in the previous with
statement. In this case, Ws.

Neal Zimm wrote:

Hi All,
The function below resides in an Add-In.
It runs as part of a macro executed at the open event.

The open event code is: Call Wbk_Open(Status)
The Wbk_Open sub is also in the addin and uses the function.

All is well when the wbk opened after the personal.xls is NOT read only.
All is well when the read only wbk is opened after a 'regular' wbk.
I get the 1004 run time error at the set line noted below when the 1st wbk
opened after the personal is a backup wbk opened as read only.

I've stepped thru the code and all variables seem to have good values.
MsoMaxRow is a public constant in Declarations in the AddIn.
The read only wbk and the one from which is was created both reference the
Add IN.

I don't know where to go from here.
Thanks.

Function zFind_Row1Cf(Ws As Worksheet, sLookFor As String, _
Col As Integer, FmRow As Long, ToRow As Long, _
bXlWhole As Boolean) As Long
'Return the row of the cell where a string value is found in one column.
'Zero returned if not found. bXlwhole= true= string occupies entire cell.
Dim It As Range, WhoOrPrt

If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart
If FmRow < 1 Then FmRow = 1
If ToRow < 1 Or ToRow MSoMaxRow Then ToRow = MSoMaxRow

'the set gets the run time error and shows 'yellow'
Set It = Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

If Not It Is Nothing Then zFind_Row1Cf = It.Row
End Function
--
Neal Z

--

Dave Peterson

#1. But you did want repeated references to that worksheet--.range, .cells()
and .cells().

#2. How about this:

dim ws1 as worksheet
dim ws2 as worksheet

set ws1 = worksheets("sheet1")
set ws2 = worksheets("sheet2")

with ws1
ws2.Range(.range("a1").value).value = "hi"
end with

Where Ws1.range("a1") contained a value of X99. (Kind of an =indirect() VBA
example.)

Just because it's inside the ()'s, doesn't mean I want it to refer to ws2.


Neal Zimm wrote:

Dear Dave,
Thanks, it worked, but i don't understand why. Please bear with me for
minute. The devil of it is that I HAD the 'With Ws' construct. I read other
postings that said unless you have repeated refs to the With object, there
wasn't much efficiency gained so I changed the code to that which failed.

Seems to me that if I have:
Set It = Ws.range(cells(f,c),cells(t,c)).Find(whatever)

1) Here's the biggie: Even if the specified cells are 'pointing' to the
wrong sheet, WHY do I get the run time error rather than an incorrect result
of the find since I might be looking at data on a worksheet that's not the
one indicated by the Ws object ?

2) I'm kinda OK in accepting that the cells may not be in the Ws but
........... Ws.range(Ws.cells(etc),Ws.cells(etc)) "seems" redundant. That
which is inside the parenthesis of Ws.range "should" belong to Ws.range. Any
thoughts ?

thanks again,
Neal

--
Neal Z

"Dave Peterson" wrote:

This line:

Set It = Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

has unqualified range references: cells() will belong to the activesheet if the
code is in a general module. Or it will refer to the sheet owning the code if
it's behind a worksheet module.

You could use:

Set It = Ws.Range(ws.Cells(FmRow, Col), ws.Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

to make sure that those cells() belong to the Ws worksheet.

Or to save typing (and easier to copy|paste to other procedures):

with ws
Set It = .Range(.Cells(FmRow, Col), .Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)
end with

All those .range(), .cells() will refer to the object in the previous with
statement. In this case, Ws.

Neal Zimm wrote:

Hi All,
The function below resides in an Add-In.
It runs as part of a macro executed at the open event.

The open event code is: Call Wbk_Open(Status)
The Wbk_Open sub is also in the addin and uses the function.

All is well when the wbk opened after the personal.xls is NOT read only.
All is well when the read only wbk is opened after a 'regular' wbk.
I get the 1004 run time error at the set line noted below when the 1st wbk
opened after the personal is a backup wbk opened as read only.

I've stepped thru the code and all variables seem to have good values.
MsoMaxRow is a public constant in Declarations in the AddIn.
The read only wbk and the one from which is was created both reference the
Add IN.

I don't know where to go from here.
Thanks.

Function zFind_Row1Cf(Ws As Worksheet, sLookFor As String, _
Col As Integer, FmRow As Long, ToRow As Long, _
bXlWhole As Boolean) As Long
'Return the row of the cell where a string value is found in one column.
'Zero returned if not found. bXlwhole= true= string occupies entire cell.
Dim It As Range, WhoOrPrt

If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart
If FmRow < 1 Then FmRow = 1
If ToRow < 1 Or ToRow MSoMaxRow Then ToRow = MSoMaxRow

'the set gets the run time error and shows 'yellow'
Set It = Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

If Not It Is Nothing Then zFind_Row1Cf = It.Row
End Function
--
Neal Z

--

Dave Peterson


--

Dave Peterson

I can't find any documentation on MSoMaxRow. try this instead

If ToRow < 1 Or ToRow Rows.Count Then ToRow = Rows.Count

"Neal Zimm" wrote:

Hi All,
The function below resides in an Add-In.
It runs as part of a macro executed at the open event.

The open event code is: Call Wbk_Open(Status)
The Wbk_Open sub is also in the addin and uses the function.

All is well when the wbk opened after the personal.xls is NOT read only.
All is well when the read only wbk is opened after a 'regular' wbk.
I get the 1004 run time error at the set line noted below when the 1st wbk
opened after the personal is a backup wbk opened as read only.

I've stepped thru the code and all variables seem to have good values.
MsoMaxRow is a public constant in Declarations in the AddIn.
The read only wbk and the one from which is was created both reference the
Add IN.

I don't know where to go from here.
Thanks.


Function zFind_Row1Cf(Ws As Worksheet, sLookFor As String, _
Col As Integer, FmRow As Long, ToRow As Long, _
bXlWhole As Boolean) As Long
'Return the row of the cell where a string value is found in one column.
'Zero returned if not found. bXlwhole= true= string occupies entire cell.
Dim It As Range, WhoOrPrt

If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart
If FmRow < 1 Then FmRow = 1
If ToRow < 1 Or ToRow MSoMaxRow Then ToRow = MSoMaxRow

'the set gets the run time error and shows 'yellow'
Set It = Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

If Not It Is Nothing Then zFind_Row1Cf = It.Row
End Function
--
Neal Z

Thanks, but I dreamed it up so the doc is in my code:

'Max row in a worksheet. Change if Msoft changes rows in a ws.
Public Const MsoMaxRow as long = 65536
--
Neal Z


"Joel" wrote:

I can't find any documentation on MSoMaxRow. try this instead

If ToRow < 1 Or ToRow Rows.Count Then ToRow = Rows.Count

"Neal Zimm" wrote:

Hi All,
The function below resides in an Add-In.
It runs as part of a macro executed at the open event.

The open event code is: Call Wbk_Open(Status)
The Wbk_Open sub is also in the addin and uses the function.

All is well when the wbk opened after the personal.xls is NOT read only.
All is well when the read only wbk is opened after a 'regular' wbk.
I get the 1004 run time error at the set line noted below when the 1st wbk
opened after the personal is a backup wbk opened as read only.

I've stepped thru the code and all variables seem to have good values.
MsoMaxRow is a public constant in Declarations in the AddIn.
The read only wbk and the one from which is was created both reference the
Add IN.

I don't know where to go from here.
Thanks.


Function zFind_Row1Cf(Ws As Worksheet, sLookFor As String, _
Col As Integer, FmRow As Long, ToRow As Long, _
bXlWhole As Boolean) As Long
'Return the row of the cell where a string value is found in one column.
'Zero returned if not found. bXlwhole= true= string occupies entire cell.
Dim It As Range, WhoOrPrt

If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart
If FmRow < 1 Then FmRow = 1
If ToRow < 1 Or ToRow MSoMaxRow Then ToRow = MSoMaxRow

'the set gets the run time error and shows 'yellow'
Set It = Ws.Range(Cells(FmRow, Col), Cells(ToRow, Col)).Find(sLookFor, _
LookIn:=xlValues, LookAt:=WhoOrPrt)

If Not It Is Nothing Then zFind_Row1Cf = It.Row
End Function
--
Neal Z