Greetings,

When using Range.Find on a single celled range ($A$1), Find returns cell
outside that Range.

When attempting to run the following VB6 code (see below and/or attached Zip
file) automating Excel 2003, against an Excel 2003 File (FindNextTest.xls)
containing one worksheet with only one row of values, with "Column A" text
in A1 and "Column B" text in B1. The Find method on a Range set to
$A$1(single cell) returns a cell outside the range (in this case, $B$1). It
appears as though it disregards the range it was told to look within and
looks within the entire spreadsheet.

In the test Excel file the initial Range Cells(1,1) = Cells(1,1) (i.e.
$A$1) contains no instance of "Column B". The first "Column B" is in the
cell $B$1. So the expectation here would have been for then Find() on range
$A$1 to return null, instead oCellFound is set to $B$1, the first cell with
"Column B" in it. Additionally, we found that if the initial search range
was expanded to not just be a single cell (i.e. made the Range more than a
single cell, like Cells(1,1), Cells(2,1) ($A$1:$A$2) ) then the Find() does
return null and not $B$1.

Additionally if we take that return from Range.Find() and feed it into a
second call Range.FindNext() we get the Error 1004 Unable to get the
FindNext property of the Range class because the initial return $B$1 is
outside of the $A$1 range, meaning that a cell returned from Range.Find()
may not be valid to pass into Range.FindNext().

Is the code below calling the Range.Find() incorrectly? Is there some
different behavior for single celled range versus multiple cell range as far
as Find staying withing the Range? According to docs it seems that
Range.Find() should either return Null or return a cell within the Range,
but there should not be any circumstances under which Range.Find() returns a
cell outside of the Range, no?

Thanks for any help that can be provided.
-Frank Jones

Vb6 code below:
============

VERSION 5.00
Begin VB.Form frmMain
Caption = "FindNext Test"
ClientHeight = 2790
ClientLeft = 60
ClientTop = 345
ClientWidth = 6000
LinkTopic = "Form1"
ScaleHeight = 2790
ScaleWidth = 6000
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton Command1
Caption = "Go"
Height = 375
Left = 1080
TabIndex = 4
Top = 2280
Width = 1335
End
Begin VB.TextBox Text2
Height = 975
Left = 720
MultiLine = -1 'True
ScrollBars = 2 'Vertical
TabIndex = 1
Top = 1080
Width = 5055
End
Begin VB.TextBox Text1
Height = 375
Left = 840
TabIndex = 0
Top = 480
Width = 4815
End
Begin VB.Label Label2
Caption = "Result:"
Height = 495
Left = 120
TabIndex = 3
Top = 1080
Width = 735
End
Begin VB.Label Label1
Caption = "Excel File:"
Height = 375
Left = 120
TabIndex = 2
Top = 480
Width = 735
End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False


Private Sub Form_Load()
Me.Text1.Text = App.Path & "\FindNextTest.xls"
End Sub

Private Sub Command1_Click()
Me.Text2.Text = FindTest(Text1.Text)
End Sub

Function FindTest(strFilePath As String) As String

Dim oExcel As Excel.Application
Dim oSearchRange As Range
Dim oCellFound As Range
Dim oWorksheet As Worksheet
Dim oWorkbook As Workbook

Set oExcel = New Excel.Application
Set oWorkbook = oExcel.Workbooks.Open(strFilePath, UpdateLinks:=0,
ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set oWorksheet = oWorkbook.Worksheets(1)

' Note: If this search Range is set to be more than just $A$1 then it
works as expected
Set oSearchRange = oWorksheet.Range(oWorksheet.Cells(1, 1),
oWorksheet.Cells(1, 1))

Set oCellFound = oSearchRange.Find(What:="Column B",
LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlPart)

If oCellFound Is Nothing Then
FindTest = "It Worked - Should not have found a ""Column B"" in
Range " & oSearchRange.Address
Else
' This cell is outside the search range
FindTest = "Range.Find on range " & oSearchRange.Address & "
returned cell " & oCellFound.Address

' This line will get Error 1004 Unable to get the FindNext property
of the Range class
FindTest = FindTest & vbCrLf & "FindNext(" & oCellFound.Address & ")
Result "

On Error Resume Next
Set oCellFound = oSearchRange.FindNext(oCellFound)
If Err.Number < 0 Then
FindTest = FindTest & "Error Occurred: " & Err.Number & " " &
Err.Description
Else
FindTest = FindTest & "Succeeded!"
End If
On Error GoTo 0

End If

Call oWorkbook.Close(SaveChanges:=False)
Call oExcel.Quit
End Function

When you run Find with a single cell selected VBA always assumes that you
want to search the entire worksheet. AFAIK this is by design.

--

Vasant

"Frank Jones" wrote in message
...
Greetings,

When using Range.Find on a single celled range ($A$1), Find returns cell
outside that Range.

When attempting to run the following VB6 code (see below and/or attached
Zip
file) automating Excel 2003, against an Excel 2003 File (FindNextTest.xls)
containing one worksheet with only one row of values, with "Column A" text
in A1 and "Column B" text in B1. The Find method on a Range set to
$A$1(single cell) returns a cell outside the range (in this case, $B$1).
It
appears as though it disregards the range it was told to look within and
looks within the entire spreadsheet.

In the test Excel file the initial Range Cells(1,1) = Cells(1,1) (i.e.
$A$1) contains no instance of "Column B". The first "Column B" is in
the
cell $B$1. So the expectation here would have been for then Find() on
range
$A$1 to return null, instead oCellFound is set to $B$1, the first cell
with
"Column B" in it. Additionally, we found that if the initial search range
was expanded to not just be a single cell (i.e. made the Range more than a
single cell, like Cells(1,1), Cells(2,1) ($A$1:$A$2) ) then the Find()
does
return null and not $B$1.

Additionally if we take that return from Range.Find() and feed it into a
second call Range.FindNext() we get the Error 1004 Unable to get the
FindNext property of the Range class because the initial return $B$1 is
outside of the $A$1 range, meaning that a cell returned from Range.Find()
may not be valid to pass into Range.FindNext().

Is the code below calling the Range.Find() incorrectly? Is there some
different behavior for single celled range versus multiple cell range as
far
as Find staying withing the Range? According to docs it seems that
Range.Find() should either return Null or return a cell within the Range,
but there should not be any circumstances under which Range.Find() returns
a
cell outside of the Range, no?

Thanks for any help that can be provided.
-Frank Jones

Vb6 code below:
============

VERSION 5.00
Begin VB.Form frmMain
Caption = "FindNext Test"
ClientHeight = 2790
ClientLeft = 60
ClientTop = 345
ClientWidth = 6000
LinkTopic = "Form1"
ScaleHeight = 2790
ScaleWidth = 6000
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton Command1
Caption = "Go"
Height = 375
Left = 1080
TabIndex = 4
Top = 2280
Width = 1335
End
Begin VB.TextBox Text2
Height = 975
Left = 720
MultiLine = -1 'True
ScrollBars = 2 'Vertical
TabIndex = 1
Top = 1080
Width = 5055
End
Begin VB.TextBox Text1
Height = 375
Left = 840
TabIndex = 0
Top = 480
Width = 4815
End
Begin VB.Label Label2
Caption = "Result:"
Height = 495
Left = 120
TabIndex = 3
Top = 1080
Width = 735
End
Begin VB.Label Label1
Caption = "Excel File:"
Height = 375
Left = 120
TabIndex = 2
Top = 480
Width = 735
End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False


Private Sub Form_Load()
Me.Text1.Text = App.Path & "\FindNextTest.xls"
End Sub

Private Sub Command1_Click()
Me.Text2.Text = FindTest(Text1.Text)
End Sub

Function FindTest(strFilePath As String) As String

Dim oExcel As Excel.Application
Dim oSearchRange As Range
Dim oCellFound As Range
Dim oWorksheet As Worksheet
Dim oWorkbook As Workbook

Set oExcel = New Excel.Application
Set oWorkbook = oExcel.Workbooks.Open(strFilePath, UpdateLinks:=0,
ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set oWorksheet = oWorkbook.Worksheets(1)

' Note: If this search Range is set to be more than just $A$1 then it
works as expected
Set oSearchRange = oWorksheet.Range(oWorksheet.Cells(1, 1),
oWorksheet.Cells(1, 1))

Set oCellFound = oSearchRange.Find(What:="Column B",
LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlPart)

If oCellFound Is Nothing Then
FindTest = "It Worked - Should not have found a ""Column B"" in
Range " & oSearchRange.Address
Else
' This cell is outside the search range
FindTest = "Range.Find on range " & oSearchRange.Address & "
returned cell " & oCellFound.Address

' This line will get Error 1004 Unable to get the FindNext
property
of the Range class
FindTest = FindTest & vbCrLf & "FindNext(" & oCellFound.Address &
")
Result "

On Error Resume Next
Set oCellFound = oSearchRange.FindNext(oCellFound)
If Err.Number < 0 Then
FindTest = FindTest & "Error Occurred: " & Err.Number & " " &
Err.Description
Else
FindTest = FindTest & "Succeeded!"
End If
On Error GoTo 0

End If

Call oWorkbook.Close(SaveChanges:=False)
Call oExcel.Quit
End Function

PS Please don't attach files; it's a frowned-upon practice.

--

Vasant

"Frank Jones" wrote in message
...
Greetings,

When using Range.Find on a single celled range ($A$1), Find returns cell
outside that Range.

When attempting to run the following VB6 code (see below and/or attached
Zip
file) automating Excel 2003, against an Excel 2003 File (FindNextTest.xls)
containing one worksheet with only one row of values, with "Column A" text
in A1 and "Column B" text in B1. The Find method on a Range set to
$A$1(single cell) returns a cell outside the range (in this case, $B$1).
It
appears as though it disregards the range it was told to look within and
looks within the entire spreadsheet.

In the test Excel file the initial Range Cells(1,1) = Cells(1,1) (i.e.
$A$1) contains no instance of "Column B". The first "Column B" is in
the
cell $B$1. So the expectation here would have been for then Find() on
range
$A$1 to return null, instead oCellFound is set to $B$1, the first cell
with
"Column B" in it. Additionally, we found that if the initial search range
was expanded to not just be a single cell (i.e. made the Range more than a
single cell, like Cells(1,1), Cells(2,1) ($A$1:$A$2) ) then the Find()
does
return null and not $B$1.

Additionally if we take that return from Range.Find() and feed it into a
second call Range.FindNext() we get the Error 1004 Unable to get the
FindNext property of the Range class because the initial return $B$1 is
outside of the $A$1 range, meaning that a cell returned from Range.Find()
may not be valid to pass into Range.FindNext().

Is the code below calling the Range.Find() incorrectly? Is there some
different behavior for single celled range versus multiple cell range as
far
as Find staying withing the Range? According to docs it seems that
Range.Find() should either return Null or return a cell within the Range,
but there should not be any circumstances under which Range.Find() returns
a
cell outside of the Range, no?

Thanks for any help that can be provided.
-Frank Jones

Vb6 code below:
============

VERSION 5.00
Begin VB.Form frmMain
Caption = "FindNext Test"
ClientHeight = 2790
ClientLeft = 60
ClientTop = 345
ClientWidth = 6000
LinkTopic = "Form1"
ScaleHeight = 2790
ScaleWidth = 6000
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton Command1
Caption = "Go"
Height = 375
Left = 1080
TabIndex = 4
Top = 2280
Width = 1335
End
Begin VB.TextBox Text2
Height = 975
Left = 720
MultiLine = -1 'True
ScrollBars = 2 'Vertical
TabIndex = 1
Top = 1080
Width = 5055
End
Begin VB.TextBox Text1
Height = 375
Left = 840
TabIndex = 0
Top = 480
Width = 4815
End
Begin VB.Label Label2
Caption = "Result:"
Height = 495
Left = 120
TabIndex = 3
Top = 1080
Width = 735
End
Begin VB.Label Label1
Caption = "Excel File:"
Height = 375
Left = 120
TabIndex = 2
Top = 480
Width = 735
End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False


Private Sub Form_Load()
Me.Text1.Text = App.Path & "\FindNextTest.xls"
End Sub

Private Sub Command1_Click()
Me.Text2.Text = FindTest(Text1.Text)
End Sub

Function FindTest(strFilePath As String) As String

Dim oExcel As Excel.Application
Dim oSearchRange As Range
Dim oCellFound As Range
Dim oWorksheet As Worksheet
Dim oWorkbook As Workbook

Set oExcel = New Excel.Application
Set oWorkbook = oExcel.Workbooks.Open(strFilePath, UpdateLinks:=0,
ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set oWorksheet = oWorkbook.Worksheets(1)

' Note: If this search Range is set to be more than just $A$1 then it
works as expected
Set oSearchRange = oWorksheet.Range(oWorksheet.Cells(1, 1),
oWorksheet.Cells(1, 1))

Set oCellFound = oSearchRange.Find(What:="Column B",
LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlPart)

If oCellFound Is Nothing Then
FindTest = "It Worked - Should not have found a ""Column B"" in
Range " & oSearchRange.Address
Else
' This cell is outside the search range
FindTest = "Range.Find on range " & oSearchRange.Address & "
returned cell " & oCellFound.Address

' This line will get Error 1004 Unable to get the FindNext
property
of the Range class
FindTest = FindTest & vbCrLf & "FindNext(" & oCellFound.Address &
")
Result "

On Error Resume Next
Set oCellFound = oSearchRange.FindNext(oCellFound)
If Err.Number < 0 Then
FindTest = FindTest & "Error Occurred: " & Err.Number & " " &
Err.Description
Else
FindTest = FindTest & "Succeeded!"
End If
On Error GoTo 0

End If

Call oWorkbook.Close(SaveChanges:=False)
Call oExcel.Quit
End Function

Hi Vasant,

Doing a manual search with a single cell selected this is certainly true and
is equivalent to the VBA form: Cells.Find.
In VBA,however, I do not think this is true. Testing under xl2k, a search
limited to a single cell gives the expected results for me.

This, however,begs the question of why A single cell search would be made in
VBA. Interrogation of the cells value property would appear simpler!

---
Regards
Norman


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
When you run Find with a single cell selected VBA always assumes that you
want to search the entire worksheet. AFAIK this is by design.

--

Vasant

"Frank Jones" wrote in message
...
Greetings,

When using Range.Find on a single celled range ($A$1), Find returns cell
outside that Range.

When attempting to run the following VB6 code (see below and/or attached
Zip
file) automating Excel 2003, against an Excel 2003 File
(FindNextTest.xls)
containing one worksheet with only one row of values, with "Column A"
text
in A1 and "Column B" text in B1. The Find method on a Range set to
$A$1(single cell) returns a cell outside the range (in this case, $B$1).
It
appears as though it disregards the range it was told to look within and
looks within the entire spreadsheet.

In the test Excel file the initial Range Cells(1,1) = Cells(1,1) (i.e.
$A$1) contains no instance of "Column B". The first "Column B" is in
the
cell $B$1. So the expectation here would have been for then Find() on
range
$A$1 to return null, instead oCellFound is set to $B$1, the first cell
with
"Column B" in it. Additionally, we found that if the initial search
range
was expanded to not just be a single cell (i.e. made the Range more than
a
single cell, like Cells(1,1), Cells(2,1) ($A$1:$A$2) ) then the Find()
does
return null and not $B$1.

Additionally if we take that return from Range.Find() and feed it into a
second call Range.FindNext() we get the Error 1004 Unable to get the
FindNext property of the Range class because the initial return $B$1 is
outside of the $A$1 range, meaning that a cell returned from
Range.Find()
may not be valid to pass into Range.FindNext().

Is the code below calling the Range.Find() incorrectly? Is there some
different behavior for single celled range versus multiple cell range as
far
as Find staying withing the Range? According to docs it seems that
Range.Find() should either return Null or return a cell within the
Range,
but there should not be any circumstances under which Range.Find()
returns
a
cell outside of the Range, no?

Thanks for any help that can be provided.
-Frank Jones

Vb6 code below:
============

VERSION 5.00
Begin VB.Form frmMain
Caption = "FindNext Test"
ClientHeight = 2790
ClientLeft = 60
ClientTop = 345
ClientWidth = 6000
LinkTopic = "Form1"
ScaleHeight = 2790
ScaleWidth = 6000
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton Command1
Caption = "Go"
Height = 375
Left = 1080
TabIndex = 4
Top = 2280
Width = 1335
End
Begin VB.TextBox Text2
Height = 975
Left = 720
MultiLine = -1 'True
ScrollBars = 2 'Vertical
TabIndex = 1
Top = 1080
Width = 5055
End
Begin VB.TextBox Text1
Height = 375
Left = 840
TabIndex = 0
Top = 480
Width = 4815
End
Begin VB.Label Label2
Caption = "Result:"
Height = 495
Left = 120
TabIndex = 3
Top = 1080
Width = 735
End
Begin VB.Label Label1
Caption = "Excel File:"
Height = 375
Left = 120
TabIndex = 2
Top = 480
Width = 735
End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False


Private Sub Form_Load()
Me.Text1.Text = App.Path & "\FindNextTest.xls"
End Sub

Private Sub Command1_Click()
Me.Text2.Text = FindTest(Text1.Text)
End Sub

Function FindTest(strFilePath As String) As String

Dim oExcel As Excel.Application
Dim oSearchRange As Range
Dim oCellFound As Range
Dim oWorksheet As Worksheet
Dim oWorkbook As Workbook

Set oExcel = New Excel.Application
Set oWorkbook = oExcel.Workbooks.Open(strFilePath, UpdateLinks:=0,
ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set oWorksheet = oWorkbook.Worksheets(1)

' Note: If this search Range is set to be more than just $A$1 then
it
works as expected
Set oSearchRange = oWorksheet.Range(oWorksheet.Cells(1, 1),
oWorksheet.Cells(1, 1))

Set oCellFound = oSearchRange.Find(What:="Column B",
LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlPart)

If oCellFound Is Nothing Then
FindTest = "It Worked - Should not have found a ""Column B"" in
Range " & oSearchRange.Address
Else
' This cell is outside the search range
FindTest = "Range.Find on range " & oSearchRange.Address & "
returned cell " & oCellFound.Address

' This line will get Error 1004 Unable to get the FindNext
property
of the Range class
FindTest = FindTest & vbCrLf & "FindNext(" & oCellFound.Address
&
")
Result "

On Error Resume Next
Set oCellFound = oSearchRange.FindNext(oCellFound)
If Err.Number < 0 Then
FindTest = FindTest & "Error Occurred: " & Err.Number & " "
&
Err.Description
Else
FindTest = FindTest & "Succeeded!"
End If
On Error GoTo 0

End If

Call oWorkbook.Close(SaveChanges:=False)
Call oExcel.Quit
End Function

Thank you for the prompt reply

The behavior you described is definitely what is observed, however the documentation seems misleading

The documentation for the Range object Find method states that if you do not specify the "After" parameter in the Find parameters, the search begins in the upper left corner of the range, no exception for a Range that contains only a single cell. In the single cell case it appears to search the entire worksheet (as you have stated) but starting in the cell after the cell in the range

The code does not actually select any cells before the Find, it creates a Range out of one cell then calls Find method. If that is what is meant by "selected" then everything seems to work as explained

Is this behavior of the Range.Find() method documented anywhere? Could not find anything about special case for single cell Range
Thanks


----- Vasant Nanavati wrote: ----

When you run Find with a single cell selected VBA always assumes that yo
want to search the entire worksheet. AFAIK this is by design

--

Vasan

"Frank Jones" wrote in messag
..
Greetings
When using Range.Find on a single celled range ($A$1), Find returns cel
outside that Range
When attempting to run the following VB6 code (see below and/or attache
Zi
file) automating Excel 2003, against an Excel 2003 File (FindNextTest.xls
containing one worksheet with only one row of values, with "Column A" tex
in A1 and "Column B" text in B1. The Find method on a Range set t
$A$1(single cell) returns a cell outside the range (in this case, $B$1)
I
appears as though it disregards the range it was told to look within an
looks within the entire spreadsheet
In the test Excel file the initial Range Cells(1,1) = Cells(1,1) (i.e
$A$1) contains no instance of "Column B". The first "Column B" is i
th
cell $B$1. So the expectation here would have been for then Find() o
rang
$A$1 to return null, instead oCellFound is set to $B$1, the first cel
wit
"Column B" in it. Additionally, we found that if the initial search rang
was expanded to not just be a single cell (i.e. made the Range more than
single cell, like Cells(1,1), Cells(2,1) ($A$1:$A$2) ) then the Find(
doe
return null and not $B$1
Additionally if we take that return from Range.Find() and feed it into
second call Range.FindNext() we get the Error 1004 Unable to get th
FindNext property of the Range class because the initial return $B$1 i
outside of the $A$1 range, meaning that a cell returned from Range.Find(
may not be valid to pass into Range.FindNext()
Is the code below calling the Range.Find() incorrectly? Is there som
different behavior for single celled range versus multiple cell range a
fa
as Find staying withing the Range? According to docs it seems tha
Range.Find() should either return Null or return a cell within the Range
but there should not be any circumstances under which Range.Find() return

cell outside of the Range, no
Thanks for any help that can be provided
-Frank Jone
Vb6 code below
===========
VERSION 5.0
Begin VB.Form frmMai
Caption = "FindNext Test
ClientHeight = 279
ClientLeft = 6
ClientTop = 34
ClientWidth = 600
LinkTopic = "Form1
ScaleHeight = 279
ScaleWidth = 600
StartUpPosition = 3 'Windows Defaul
Begin VB.CommandButton Command
Caption = "Go
Height = 37
Left = 108
TabIndex =
Top = 228
Width = 133
End
Begin VB.TextBox Text2
Height = 975
Left = 720
MultiLine = -1 'True
ScrollBars = 2 'Vertical
TabIndex = 1
Top = 1080
Width = 5055
End
Begin VB.TextBox Text1
Height = 375
Left = 840
TabIndex = 0
Top = 480
Width = 4815
End
Begin VB.Label Label2
Caption = "Result:"
Height = 495
Left = 120
TabIndex = 3
Top = 1080
Width = 735
End
Begin VB.Label Label1
Caption = "Excel File:"
Height = 375
Left = 120
TabIndex = 2
Top = 480
Width = 735
End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Form_Load()
Me.Text1.Text = App.Path & "\FindNextTest.xls"
End Sub
Private Sub Command1_Click()
Me.Text2.Text = FindTest(Text1.Text)
End Sub
Function FindTest(strFilePath As String) As String
Dim oExcel As Excel.Application
Dim oSearchRange As Range
Dim oCellFound As Range
Dim oWorksheet As Worksheet
Dim oWorkbook As Workbook
Set oExcel = New Excel.Application
Set oWorkbook = oExcel.Workbooks.Open(strFilePath, UpdateLinks:=0,
ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set oWorksheet = oWorkbook.Worksheets(1)
' Note: If this search Range is set to be more than just $A$1 then it
works as expected
Set oSearchRange = oWorksheet.Range(oWorksheet.Cells(1, 1),
oWorksheet.Cells(1, 1))
Set oCellFound = oSearchRange.Find(What:="Column B",
LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlPart)
If oCellFound Is Nothing Then
FindTest = "It Worked - Should not have found a ""Column B"" in
Range " & oSearchRange.Address
Else
' This cell is outside the search range
FindTest = "Range.Find on range " & oSearchRange.Address & "
returned cell " & oCellFound.Address
' This line will get Error 1004 Unable to get the FindNext
property
of the Range class
FindTest = FindTest & vbCrLf & "FindNext(" & oCellFound.Address &
")
Result "
On Error Resume Next
Set oCellFound = oSearchRange.FindNext(oCellFound)
If Err.Number < 0 Then
FindTest = FindTest & "Error Occurred: " & Err.Number & " " & Err.Description
Else
FindTest = FindTest & "Succeeded!"
End If
On Error GoTo 0
End If
Call oWorkbook.Close(SaveChanges:=False)
Call oExcel.Quit
End Function

Thanks, Norman; I don't know what I was thinking. In VBA, of course, Find is
qualified by the range to be searched, so what range is selected is
irrelevant.

Regards,

Vasant.

"Norman Jones" wrote in message
...
Hi Vasant,

Doing a manual search with a single cell selected this is certainly true
and
is equivalent to the VBA form: Cells.Find.
In VBA,however, I do not think this is true. Testing under xl2k, a search
limited to a single cell gives the expected results for me.

This, however,begs the question of why A single cell search would be made
in
VBA. Interrogation of the cells value property would appear simpler!

---
Regards
Norman


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
When you run Find with a single cell selected VBA always assumes that
you
want to search the entire worksheet. AFAIK this is by design.

--

Vasant

"Frank Jones" wrote in message
...
Greetings,

When using Range.Find on a single celled range ($A$1), Find returns
cell
outside that Range.

When attempting to run the following VB6 code (see below and/or
attached
Zip
file) automating Excel 2003, against an Excel 2003 File
(FindNextTest.xls)
containing one worksheet with only one row of values, with "Column A"
text
in A1 and "Column B" text in B1. The Find method on a Range set to
$A$1(single cell) returns a cell outside the range (in this case,
$B$1).
It
appears as though it disregards the range it was told to look within
and
looks within the entire spreadsheet.

In the test Excel file the initial Range Cells(1,1) = Cells(1,1)
(i.e.
$A$1) contains no instance of "Column B". The first "Column B" is
in
the
cell $B$1. So the expectation here would have been for then Find() on
range
$A$1 to return null, instead oCellFound is set to $B$1, the first cell
with
"Column B" in it. Additionally, we found that if the initial search
range
was expanded to not just be a single cell (i.e. made the Range more
than
a
single cell, like Cells(1,1), Cells(2,1) ($A$1:$A$2) ) then the
Find()
does
return null and not $B$1.

Additionally if we take that return from Range.Find() and feed it into
a
second call Range.FindNext() we get the Error 1004 Unable to get the
FindNext property of the Range class because the initial return $B$1
is
outside of the $A$1 range, meaning that a cell returned from
Range.Find()
may not be valid to pass into Range.FindNext().

Is the code below calling the Range.Find() incorrectly? Is there some
different behavior for single celled range versus multiple cell range
as
far
as Find staying withing the Range? According to docs it seems that
Range.Find() should either return Null or return a cell within the
Range,
but there should not be any circumstances under which Range.Find()
returns
a
cell outside of the Range, no?

Thanks for any help that can be provided.
-Frank Jones

Vb6 code below:
============

VERSION 5.00
Begin VB.Form frmMain
Caption = "FindNext Test"
ClientHeight = 2790
ClientLeft = 60
ClientTop = 345
ClientWidth = 6000
LinkTopic = "Form1"
ScaleHeight = 2790
ScaleWidth = 6000
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton Command1
Caption = "Go"
Height = 375
Left = 1080
TabIndex = 4
Top = 2280
Width = 1335
End
Begin VB.TextBox Text2
Height = 975
Left = 720
MultiLine = -1 'True
ScrollBars = 2 'Vertical
TabIndex = 1
Top = 1080
Width = 5055
End
Begin VB.TextBox Text1
Height = 375
Left = 840
TabIndex = 0
Top = 480
Width = 4815
End
Begin VB.Label Label2
Caption = "Result:"
Height = 495
Left = 120
TabIndex = 3
Top = 1080
Width = 735
End
Begin VB.Label Label1
Caption = "Excel File:"
Height = 375
Left = 120
TabIndex = 2
Top = 480
Width = 735
End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False


Private Sub Form_Load()
Me.Text1.Text = App.Path & "\FindNextTest.xls"
End Sub

Private Sub Command1_Click()
Me.Text2.Text = FindTest(Text1.Text)
End Sub

Function FindTest(strFilePath As String) As String

Dim oExcel As Excel.Application
Dim oSearchRange As Range
Dim oCellFound As Range
Dim oWorksheet As Worksheet
Dim oWorkbook As Workbook

Set oExcel = New Excel.Application
Set oWorkbook = oExcel.Workbooks.Open(strFilePath, UpdateLinks:=0,
ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set oWorksheet = oWorkbook.Worksheets(1)

' Note: If this search Range is set to be more than just $A$1 then
it
works as expected
Set oSearchRange = oWorksheet.Range(oWorksheet.Cells(1, 1),
oWorksheet.Cells(1, 1))

Set oCellFound = oSearchRange.Find(What:="Column B",
LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlPart)

If oCellFound Is Nothing Then
FindTest = "It Worked - Should not have found a ""Column B""
in
Range " & oSearchRange.Address
Else
' This cell is outside the search range
FindTest = "Range.Find on range " & oSearchRange.Address & "
returned cell " & oCellFound.Address

' This line will get Error 1004 Unable to get the FindNext
property
of the Range class
FindTest = FindTest & vbCrLf & "FindNext(" &
oCellFound.Address
&
")
Result "

On Error Resume Next
Set oCellFound = oSearchRange.FindNext(oCellFound)
If Err.Number < 0 Then
FindTest = FindTest & "Error Occurred: " & Err.Number & "
"
&
Err.Description
Else
FindTest = FindTest & "Succeeded!"
End If
On Error GoTo 0

End If

Call oWorkbook.Close(SaveChanges:=False)
Call oExcel.Quit
End Function

Hi Frank,

I can reproduce the problem on my side, as Norman said, the behavior will
not persist in VBA environment of EXCEL and will only occur when we
automation the Excel to do the find stuff.
Now I am researching the issue, if I have any new information I will get
back and post here ASAP.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks for reply Norman
"why A single cell search would be made in VBA. Interrogation of the cells value property would appear simpler!

With code that searches over a range or sometimes a single cell, we did not want to make two code paths one for the single cell case and another code path for the multicell case. Imagine that the range is dynamically calculated and could be anywhere from one cell to many cells, then you could use Range.Find in both cases. In Excel 2003 this does not appear to be the case. In Excel 2000 automated with VB6 it seemed like it worked just fine, Range.Find always returned a cell within the range


----- Norman Jones wrote: ----

Hi Vasant

Doing a manual search with a single cell selected this is certainly true an
is equivalent to the VBA form: Cells.Find
In VBA,however, I do not think this is true. Testing under xl2k, a searc
limited to a single cell gives the expected results for me

This, however,begs the question of why A single cell search would be made i
VBA. Interrogation of the cells value property would appear simpler

--
Regard
Norma


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in messag
..
When you run Find with a single cell selected VBA always assumes that yo
want to search the entire worksheet. AFAIK this is by design
--
Vasan
"Frank Jones" wrote in messag
..
Greetings
When using Range.Find on a single celled range ($A$1), Find returns cel
outside that Range
When attempting to run the following VB6 code (see below and/or attache
Zi
file) automating Excel 2003, against an Excel 2003 Fil
(FindNextTest.xls
containing one worksheet with only one row of values, with "Column A
tex
in A1 and "Column B" text in B1. The Find method on a Range set t
$A$1(single cell) returns a cell outside the range (in this case, $B$1)
I
appears as though it disregards the range it was told to look within an
looks within the entire spreadsheet
In the test Excel file the initial Range Cells(1,1) = Cells(1,1) (i.e
$A$1) contains no instance of "Column B". The first "Column B" is i
th
cell $B$1. So the expectation here would have been for then Find() o
rang
$A$1 to return null, instead oCellFound is set to $B$1, the first cel
wit
"Column B" in it. Additionally, we found that if the initial searc
rang
was expanded to not just be a single cell (i.e. made the Range more tha

single cell, like Cells(1,1), Cells(2,1) ($A$1:$A$2) ) then the Find(
doe
return null and not $B$1
Additionally if we take that return from Range.Find() and feed it into
second call Range.FindNext() we get the Error 1004 Unable to get th
FindNext property of the Range class because the initial return $B$1 i
outside of the $A$1 range, meaning that a cell returned fro
Range.Find(
may not be valid to pass into Range.FindNext()
Is the code below calling the Range.Find() incorrectly? Is there som
different behavior for single celled range versus multiple cell range a
fa
as Find staying withing the Range? According to docs it seems tha
Range.Find() should either return Null or return a cell within th
Range
but there should not be any circumstances under which Range.Find(
return

cell outside of the Range, no
Thanks for any help that can be provided
-Frank Jone
Vb6 code below
===========
VERSION 5.0
Begin VB.Form frmMai
Caption = "FindNext Test
ClientHeight = 279
ClientLeft = 6
ClientTop = 345
ClientWidth = 6000
LinkTopic = "Form1"
ScaleHeight = 2790
ScaleWidth = 6000
StartUpPosition = 3 'Windows Default
Begin VB.CommandButton Command1
Caption = "Go"
Height = 375
Left = 1080
TabIndex = 4
Top = 2280
Width = 1335
End
Begin VB.TextBox Text2
Height = 975
Left = 720
MultiLine = -1 'True
ScrollBars = 2 'Vertical
TabIndex = 1
Top = 1080
Width = 5055
End
Begin VB.TextBox Text1
Height = 375
Left = 840
TabIndex = 0
Top = 480
Width = 4815
End
Begin VB.Label Label2
Caption = "Result:"
Height = 495
Left = 120
TabIndex = 3
Top = 1080
Width = 735
End
Begin VB.Label Label1
Caption = "Excel File:"
Height = 375
Left = 120
TabIndex = 2
Top = 480
Width = 735
End
End
Attribute VB_Name = "frmMain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Form_Load()
Me.Text1.Text = App.Path & "\FindNextTest.xls"
End Sub
Private Sub Command1_Click()
Me.Text2.Text = FindTest(Text1.Text)
End Sub
Function FindTest(strFilePath As String) As String
Dim oExcel As Excel.Application
Dim oSearchRange As Range
Dim oCellFound As Range
Dim oWorksheet As Worksheet
Dim oWorkbook As Workbook
Set oExcel = New Excel.Application
Set oWorkbook = oExcel.Workbooks.Open(strFilePath, UpdateLinks:=0,
ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set oWorksheet = oWorkbook.Worksheets(1)
' Note: If this search Range is set to be more than just $A$1 then
it
works as expected
Set oSearchRange = oWorksheet.Range(oWorksheet.Cells(1, 1),
oWorksheet.Cells(1, 1))
Set oCellFound = oSearchRange.Find(What:="Column B",
LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlPart)
If oCellFound Is Nothing Then
FindTest = "It Worked - Should not have found a ""Column B"" in
Range " & oSearchRange.Address
Else
' This cell is outside the search range
FindTest = "Range.Find on range " & oSearchRange.Address & "
returned cell " & oCellFound.Address
' This line will get Error 1004 Unable to get the FindNext
property
of the Range class
FindTest = FindTest & vbCrLf & "FindNext(" & oCellFound.Address
& ")
Result "
On Error Resume Next
Set oCellFound = oSearchRange.FindNext(oCellFound)
If Err.Number < 0 Then
FindTest = FindTest & "Error Occurred: " & Err.Number & " "
& Err.Description
Else
FindTest = FindTest & "Succeeded!"
End If
On Error GoTo 0
End If
Call oWorkbook.Close(SaveChanges:=False)
Call oExcel.Quit
End Function

Hi Frank,

I am consulting it to office support engineer. The behavior seems strange. We will reply here with more information as soon as possible.

Thanks very much for your patience.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks, we await your response

----- Yan-Hong Huang[MSFT] wrote: ----

Hi Frank

I am consulting it to office support engineer. The behavior seems strange. We will reply here with more information as soon as possible.

Thanks very much for your patience

Best regards
Yanhong Huan
Microsoft Community Suppor

Get Secure! ¨C www.microsoft.com/securit
This posting is provided "AS IS" with no warranties, and confers no rights

Hi Frank,

I have taken a look at your sample, and have successfully reproduced the
problem. When running inside the VB Project, and the Range is set to a
single cell, the result is a range object outside of the searchable range.

When running inside of VBA, the code works as expected and returns a Null
object.

There does not seem to be anything wrong with how you coded the sample. The
result is expected to be a Null object.

I will go ahead and report this as a bug. In the meantime, you should be
able to work around this by checking the case where the searchable range is
only one cell. You indicated that when the range is more than a single
cell, 'Find' works properly.

Here is a code snippet that you could implement to work around this bug...

Set oSearchRange = oWorksheet.Range(oWorksheet.Cells(1, 1),
oWorksheet.Cells(1, 1))

If oSearchRange.Cells.Count = 1 Then

If InStr(oSearchRange.Text, "Column B") < 0 Then
FindTest = "It Worked - Should have found a ""Column B"" in Range
" & oSearchRange.Address
Else
FindTest = "It Worked - Should not have found a ""Column B"" in
Range " & oSearchRange.Address
End If
Else
Set oCellFound = oSearchRange.Find(What:="Column B",
LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlPart)

...

End If

Best regards,

Kendal Ferner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? Please visit the Microsoft Security & Privacy Center
(http://www.microsoft.com/security) for the latest news on security updates.

Kendal -
Thanks for the reply, for confirming the bug, and for recording the issue as
a bug.

One last thing to point out - although doing a string comparison such as
InStr on the Range.Text property will work for some cases as a replacement
for Range.Find, it will not work for the general case.

One such case is when the case when Range.Find is called with a # symbol.
Range.Find("#") will return a hit for numeric or date cells that have ####
in them because they need to be resized as well as for cells that acutally
contain a # symbol in their text. There does not seem to be anotther easy
way to find cells containing numeric or date values needs to be resized
other than to do a Range.Find using the "#" symbol.

We have found a work around for our situation by extending the Range by an
additional cell if the Range is a single-cell, then using the Range.Find on
the multi-cell range. If the find hit is on the extended cell than that hit
can be ignored and treated the same as a Null return from Range.Find.

Thanks for your assistance.


"Kendal Ferner [MSFT]" wrote in message
...
Hi Frank,

I have taken a look at your sample, and have successfully reproduced the
problem. When running inside the VB Project, and the Range is set to a
single cell, the result is a range object outside of the searchable range.

When running inside of VBA, the code works as expected and returns a Null
object.

There does not seem to be anything wrong with how you coded the sample.
The
result is expected to be a Null object.

I will go ahead and report this as a bug. In the meantime, you should be
able to work around this by checking the case where the searchable range
is
only one cell. You indicated that when the range is more than a single
cell, 'Find' works properly.

Here is a code snippet that you could implement to work around this bug...

Set oSearchRange = oWorksheet.Range(oWorksheet.Cells(1, 1),
oWorksheet.Cells(1, 1))

If oSearchRange.Cells.Count = 1 Then

If InStr(oSearchRange.Text, "Column B") < 0 Then
FindTest = "It Worked - Should have found a ""Column B"" in
Range
" & oSearchRange.Address
Else
FindTest = "It Worked - Should not have found a ""Column B"" in
Range " & oSearchRange.Address
End If
Else
Set oCellFound = oSearchRange.Find(What:="Column B",
LookIn:=XlFindLookIn.xlValues, LookAt:=XlLookAt.xlPart)

...

End If

Best regards,

Kendal Ferner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no
rights.

Are you secure? Please visit the Microsoft Security & Privacy Center
(http://www.microsoft.com/security) for the latest news on security
updates.

Hi Kendal,

Thanks very much for sharing your workaround in the community. :)

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.