F
Frederik Romanov
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
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