Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.Find returns cell outside of range when range set to single cell
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
Refer to a Single cell in a name range | Excel Worksheet Functions | |||
Range of numbers in a single cell | Excel Discussion (Misc queries) | |||
looking for range of text in a single cell | Excel Discussion (Misc queries) | |||
Need a function that finds a value in a table range and returns the cell number | Excel Programming |