Problems with SearchInRange() macro
 

Thanks for the two replies, first a simpler re-statement of the
original problem, then responses to points in the replies, then the
code for the cited examples.

I want to write a function
-------------------------------------
SearchInRange(searchStr, searchRange as Range, cellPos, strPos)

where I can search a *RANGE* of cells (and start the search at
position cellPos in this range for successive finds). Each search must
start from position strPos within each cell in the range
searchRange(cellPos:searchRange.Count).

The problem is that a statement of the form
result = searchRange(1, 1).Value
does not get evaluated by Excel.

In yesterdays post I did have result declared to be an integer
(searchRange contains string text), however this was only since I was
decomposing the original expression into simpler pieces to probe with
the debugger.

I have created a dummied down version of the original SearchInRange(),
it is SearchIn2Cells() below. It still fails on the line
result = Application.Search(searchStr, searchRange(1, 1).Value,
strPos)

and I know that it is the
searchRange(1, 1).Value

expression that is failing because the following line in
SearchInCell() does compute
result = Application.Search(searchStr, cell, strPos)

So the kernel problem is exactly as in the original post.

Any ideas and suggestions gratefully evaluated,
Yours,
Fred.


Responses
-------------------------------------
Find does not work within a Function (XL97)
See example in SearchInCell() below - Application.Search() seems to
work fine in Excel-97 SR-2

Pehaps you could make it a sub
Then I could not call it from a worksheet cell.

If I don't have an integer in searchrange(1,1), then it blows up nicely.
See example for SearchInCell() below, it finds the search string "-A?"
in the search argument "WAC-011-A-X".


Examples
-----------------
'obsolete-list.xls'!$C2:$C3 =
0.47K 250V
WAC-011-A-X

AS14 =
-A?

=SearchInCell(AS14,'obsolete-list.xls'!$C3,1) = 8
=SearchIn2Cells(AS14,'obsolete-list.xls'!$C2:$C3,1) = #VALUE!


Code
-------------------------------------
Public Function SearchInCell(searchStr, cell, strPos)
Dim result As Integer, iCell As Integer, notFound As Integer
result = Application.Search(searchStr, cell, strPos)
SearchInCell = result
End Function

Public Function SearchIn2Cells(searchStr, searchRange as Range,
strPos)
Dim result As Integer, iCell As Integer, notFound As Integer
result = Application.Search(searchStr, searchRange(1, 1).Value,
strPos)
if ( Application.IsError(result) ) Then
result = Application.Search(searchStr, searchRange(2, 1).Value,
strPos)
if ( Application.IsError(result) ) Then
SearchIn2Cells = 0
Else
SearchIn2Cells = 2
End if
Else
SearchIn2Cells = 1
End if
End Function

Problems with SearchInRange() macro
 

Since application.search will return an error or a number, you can't have Result
dimmed as an integer.

Try dimming it as a Variant.



Frederik Romanov wrote:

Thanks for the two replies, first a simpler re-statement of the
original problem, then responses to points in the replies, then the
code for the cited examples.

I want to write a function
-------------------------------------
SearchInRange(searchStr, searchRange as Range, cellPos, strPos)

where I can search a *RANGE* of cells (and start the search at
position cellPos in this range for successive finds). Each search must
start from position strPos within each cell in the range
searchRange(cellPos:searchRange.Count).

The problem is that a statement of the form
result = searchRange(1, 1).Value
does not get evaluated by Excel.

In yesterdays post I did have result declared to be an integer
(searchRange contains string text), however this was only since I was
decomposing the original expression into simpler pieces to probe with
the debugger.

I have created a dummied down version of the original SearchInRange(),
it is SearchIn2Cells() below. It still fails on the line
result = Application.Search(searchStr, searchRange(1, 1).Value,
strPos)

and I know that it is the
searchRange(1, 1).Value

expression that is failing because the following line in
SearchInCell() does compute
result = Application.Search(searchStr, cell, strPos)

So the kernel problem is exactly as in the original post.

Any ideas and suggestions gratefully evaluated,
Yours,
Fred.

Responses
-------------------------------------
Find does not work within a Function (XL97)
See example in SearchInCell() below - Application.Search() seems to
work fine in Excel-97 SR-2

Pehaps you could make it a sub
Then I could not call it from a worksheet cell.

If I don't have an integer in searchrange(1,1), then it blows up nicely.
See example for SearchInCell() below, it finds the search string "-A?"
in the search argument "WAC-011-A-X".

Examples
-----------------
'obsolete-list.xls'!$C2:$C3 =
0.47K 250V
WAC-011-A-X

AS14 =
-A?

=SearchInCell(AS14,'obsolete-list.xls'!$C3,1) = 8
=SearchIn2Cells(AS14,'obsolete-list.xls'!$C2:$C3,1) = #VALUE!

Code
-------------------------------------
Public Function SearchInCell(searchStr, cell, strPos)
Dim result As Integer, iCell As Integer, notFound As Integer
result = Application.Search(searchStr, cell, strPos)
SearchInCell = result
End Function

Public Function SearchIn2Cells(searchStr, searchRange as Range,
strPos)
Dim result As Integer, iCell As Integer, notFound As Integer
result = Application.Search(searchStr, searchRange(1, 1).Value,
strPos)
if ( Application.IsError(result) ) Then
result = Application.Search(searchStr, searchRange(2, 1).Value,
strPos)
if ( Application.IsError(result) ) Then
SearchIn2Cells = 0
Else
SearchIn2Cells = 2
End if
Else
SearchIn2Cells = 1
End if
End Function

--

Dave Peterson