F
Frederik Romanov
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
-------------------------------------
work fine in Excel-97 SR-2
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
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
-------------------------------------
See example in SearchInCell() below - Application.Search() seems toFind does not work within a Function (XL97)
work fine in Excel-97 SR-2
Then I could not call it from a worksheet cell.Pehaps you could make it a sub
See example for SearchInCell() below, it finds the search string "-A?"If I don't have an integer in searchrange(1,1), then it blows up nicely.
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