F
Frank Jones
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 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
