With VBA, I've had success building loops to find a cell using
cells(row,col) within row and column loops. I copied the
cells.find code below from recording the menu Edit Find
function to try and let Excel do the "looping" work via
the zString_Find called sub.

I'm trying for row and col to have 0 values when
a cell is found in the a__test procedure.

I've tried a couple of variations without success.
two problems:
1) when I find a cell, the msgbox shows row and column values
of 0

2) when the value is not found, I'm getting a run time 91
error, object variable or with block variable not set.
I can't seem to find the right method to not error out
when the value I'm looking for is not found.

Help please.
Thanks,
Neal



Sub a__TEST()
Dim Chars As String, Row As Integer, Col As Integer
Chars = "zzzzend"
Call zString_Find(Chars, Row, Col)
MsgBox "Found row,col " & Row & ", " & Col, , "Find this: " & Chars
End Sub


Sub zString_Find(IFindTheseChars, ORow, OCol)
' I var names are input, O vars are output
Range("a1").Activate
ORow = 0: OCol = 0
Application.DisplayAlerts = False

Cells.Find(What:=IFindTheseChars, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If ActiveCell.Row < 1 And ActiveCell.Column < 1 Then
ORow = ActiveCell.Row
OCol = ActiveCell.Column
End If

Application.DisplayAlerts = True
End Sub
--
Neal Z

Try this:

HTH
--
AP

'----------------------------------------
Sub a__TEST()
Dim Chars As String, lRow As Long, lCol As Long
Chars = "zzzzend"
Call zString_Find(Chars, lRow, lCol)
MsgBox "Found row,col " & lRow & ", " & lCol, , "Find this: " & Chars
End Sub


Sub zString_Find( _
IFindTheseChars As String, _
ByRef lRow As Long, _
ByRef lCol As Long)
' I var names are input, O vars are output
Set foundCell = Cells.Find( _
What:=IFindTheseChars, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If foundCell Is Nothing Then
MsgBox "String not found"
lRow = 0
lCol = 0
Else
lRow = foundCell.Row
lCol = foundCell.Column
End If
End Sub
'----------------------------------------------------------

Hi Ardus,
Thanks, it works fine.
Couple of follow ups:
1) What is the reason for "Dim 'ing" the zString_Find vars in the sub
statement? I usually dim them in the calling macro, I'm not sure why you use
"by ref"

2) Picking some nits,
a) any reason why you use Long for the column value?
Integer would seem to do since 256 is the max.

b) I read somewhere that when processing is done that any objects you "set"
via Set = should have: set "name" = Nothing to free them.
I added this at the end of the zString_Find Macro.
Is there any harm in this?



--
Neal Z


"Ardus Petus" wrote:

Try this:

HTH
--
AP

'----------------------------------------
Sub a__TEST()
Dim Chars As String, lRow As Long, lCol As Long
Chars = "zzzzend"
Call zString_Find(Chars, lRow, lCol)
MsgBox "Found row,col " & lRow & ", " & lCol, , "Find this: " & Chars
End Sub


Sub zString_Find( _
IFindTheseChars As String, _
ByRef lRow As Long, _
ByRef lCol As Long)
' I var names are input, O vars are output
Set foundCell = Cells.Find( _
What:=IFindTheseChars, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If foundCell Is Nothing Then
MsgBox "String not found"
lRow = 0
lCol = 0
Else
lRow = foundCell.Row
lCol = foundCell.Column
End If
End Sub
'----------------------------------------------------------

1) Sub zString_Find(...) does not "know" it's caller. All parameters should
be "dim'ed".

ByRef means that iRow & iCol parameters are transmitted "by reference" (ie:
address)
If they were declared ByVal, the called proc could not modify them

2) CVA functions Row & Column return Long.
Integer holds values up to 32767, which is not sufficient for rox # 65536

3) It's useless: once you reach the End Sub, all local variables are
destroyed

HTH
--
AP

"Neal Zimm" a écrit dans le message de
...
Hi Ardus,
Thanks, it works fine.
Couple of follow ups:
1) What is the reason for "Dim 'ing" the zString_Find vars in the sub
statement? I usually dim them in the calling macro, I'm not sure why you
use
"by ref"

2) Picking some nits,
a) any reason why you use Long for the column value?
Integer would seem to do since 256 is the max.

b) I read somewhere that when processing is done that any objects you
"set"
via Set = should have: set "name" = Nothing to free them.
I added this at the end of the zString_Find Macro.
Is there any harm in this?



--
Neal Z


"Ardus Petus" wrote:

Try this:

HTH
--
AP

'----------------------------------------
Sub a__TEST()
Dim Chars As String, lRow As Long, lCol As Long
Chars = "zzzzend"
Call zString_Find(Chars, lRow, lCol)
MsgBox "Found row,col " & lRow & ", " & lCol, , "Find this: " & Chars
End Sub


Sub zString_Find( _
IFindTheseChars As String, _
ByRef lRow As Long, _
ByRef lCol As Long)
' I var names are input, O vars are output
Set foundCell = Cells.Find( _
What:=IFindTheseChars, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If foundCell Is Nothing Then
MsgBox "String not found"
lRow = 0
lCol = 0
Else
lRow = foundCell.Row
lCol = foundCell.Column
End If
End Sub
'----------------------------------------------------------

Ardus -
Once again, thanks.
the As Long for irow and icol, I agree with Long for the row
variables, but I guess I was not clear in my question. I suppose it does not
hurt, except for a few bytes of memory allocation, but for the COLUMN
variable, I'm pretty sure integer will do fine.

Thanks.
--
Neal Z


"Ardus Petus" wrote:

1) Sub zString_Find(...) does not "know" it's caller. All parameters should
be "dim'ed".

ByRef means that iRow & iCol parameters are transmitted "by reference" (ie:
address)
If they were declared ByVal, the called proc could not modify them

2) CVA functions Row & Column return Long.
Integer holds values up to 32767, which is not sufficient for rox # 65536

3) It's useless: once you reach the End Sub, all local variables are
destroyed

HTH
--
AP

"Neal Zimm" a écrit dans le message de
...
Hi Ardus,
Thanks, it works fine.
Couple of follow ups:
1) What is the reason for "Dim 'ing" the zString_Find vars in the sub
statement? I usually dim them in the calling macro, I'm not sure why you
use
"by ref"

2) Picking some nits,
a) any reason why you use Long for the column value?
Integer would seem to do since 256 is the max.

b) I read somewhere that when processing is done that any objects you
"set"
via Set = should have: set "name" = Nothing to free them.
I added this at the end of the zString_Find Macro.
Is there any harm in this?



--
Neal Z


"Ardus Petus" wrote:

Try this:

HTH
--
AP

'----------------------------------------
Sub a__TEST()
Dim Chars As String, lRow As Long, lCol As Long
Chars = "zzzzend"
Call zString_Find(Chars, lRow, lCol)
MsgBox "Found row,col " & lRow & ", " & lCol, , "Find this: " & Chars
End Sub


Sub zString_Find( _
IFindTheseChars As String, _
ByRef lRow As Long, _
ByRef lCol As Long)
' I var names are input, O vars are output
Set foundCell = Cells.Find( _
What:=IFindTheseChars, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If foundCell Is Nothing Then
MsgBox "String not found"
lRow = 0
lCol = 0
Else
lRow = foundCell.Row
lCol = foundCell.Column
End If
End Sub
'----------------------------------------------------------