Problems with SearchInRange() macro
 

MS Excel 97 SR-2

Having problems with the implementation for

SearchInRange(AS13,'\Obsolescence\[obsolete-list.xls]A'!$C1:$C10,1,1)

1) Can't get the search() worksheet function to work in the macro
2) Debugger has quit on me.

Point 2 first: I have had an excel crash, tried to change the name of
a watch variable by editing it. Since restarting Excel, the VBA
debugger does not respect my breakpoints in SearchInRange(). Am
loathe to restart the PC - is there a reason for this, and more
importantly, a workaround?

Point 1: I cannot step over a line of the form

result = searchRange(1, 1).Value

This is the function prototype :

Public Function SearchInRange(searchStr, searchRange As Range,
cellPos, strPos)

VBA seems to perform a number of iterations on the "result =" line,
then when it returns the value of all watch variables are "Out of
context" (or whatever the message is, the debugger is now not
restarting).

Here is the full macro code (for the debug version, the obvious
reverse changes are performed to return it to "as-designed" :

' --------- Debug version
'
' Range equivalent to Search("M","Miriam McGovern",3) equals 8
' Search() is case insensitive, ? and * wildcard version of Find()
'
Public Function SearchInRange(searchStr, searchRange As Range,
cellPos, strPos)
SearchInRange = "SYNTAX : SearchInRange(searchStr, searchRange as
Range, cellPos, strPos)"
'
Dim result As Integer, iCell As Integer, notFound As Integer
If searchRange.Areas.Count = 1 Then
iCell = cellPos
notFound = 1
' While (iCell <= searchRange.Count & notFound)
iCell = 3
result = searchRange(1, 1).Value
result = searchRange(iCell, 1).Value
result = Application.Search(searchStr, result, strPos)
notFound = Application.IsError(result)
iCell = iCell + 1
' Wend
If (notFound) Then
SearchInRange = 0
Else
SearchInRange = iCell - 1
End If
End If
End Function

Problems with SearchInRange() macro
 

Find does not work within a Function (XL97)

Pehaps you could make it a sub


---
Message posted from http://www.ExcelForum.com/

Problems with SearchInRange() macro
 

What's in your SearchRange?

Result is dimmed as Integer. If I don't have an integer in searchrange(1,1),
then it blows up nicely.

And you may want to eschew the application.search and use VBA's Instr()
function. It looks like it'll do what you want.

There 4th parm is where you can specify the type of compare (vbtextcompare is
not case sensitive).

And the 1st parm is where you can start looking in the string.

As luck would have it, you must specify the starting position if you specify the
type of compare (Hi, Tom!).



Frederik Romanov wrote:

MS Excel 97 SR-2

Having problems with the implementation for

SearchInRange(AS13,'\Obsolescence\[obsolete-list.xls]A'!$C1:$C10,1,1)

1) Can't get the search() worksheet function to work in the macro
2) Debugger has quit on me.

Point 2 first: I have had an excel crash, tried to change the name of
a watch variable by editing it. Since restarting Excel, the VBA
debugger does not respect my breakpoints in SearchInRange(). Am
loathe to restart the PC - is there a reason for this, and more
importantly, a workaround?

Point 1: I cannot step over a line of the form

result = searchRange(1, 1).Value

This is the function prototype :

Public Function SearchInRange(searchStr, searchRange As Range,
cellPos, strPos)

VBA seems to perform a number of iterations on the "result =" line,
then when it returns the value of all watch variables are "Out of
context" (or whatever the message is, the debugger is now not
restarting).

Here is the full macro code (for the debug version, the obvious
reverse changes are performed to return it to "as-designed" :

' --------- Debug version
'
' Range equivalent to Search("M","Miriam McGovern",3) equals 8
' Search() is case insensitive, ? and * wildcard version of Find()
'
Public Function SearchInRange(searchStr, searchRange As Range,
cellPos, strPos)
SearchInRange = "SYNTAX : SearchInRange(searchStr, searchRange as
Range, cellPos, strPos)"
'
Dim result As Integer, iCell As Integer, notFound As Integer
If searchRange.Areas.Count = 1 Then
iCell = cellPos
notFound = 1
' While (iCell <= searchRange.Count & notFound)
iCell = 3
result = searchRange(1, 1).Value
result = searchRange(iCell, 1).Value
result = Application.Search(searchStr, result, strPos)
notFound = Application.IsError(result)
iCell = iCell + 1
' Wend
If (notFound) Then
SearchInRange = 0
Else
SearchInRange = iCell - 1
End If
End If
End Function

--

Dave Peterson