Hello everyone,



I have made two functions. Both of them use the find method, yet only one of
them actually works and I can't find out why the other one doesn't.



Here is the one that works.



Function CheckMatch(Combi_2Arg As String) As Boolean

Debug.Print Chr(13) & "**** CheckMatch ****" & Chr(13)

On Error GoTo End

Dim FoundIt As String

FoundIt = Range("Locations").Find _

(What:=Combi_2Arg, _

After:=Range("LocationBegin"), _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False) _

.Address

Debug.Print " FoundIt = " & FoundIt

If FoundIt < "" Then CheckMatch = True Else GoTo End

Debug.Print Chr(13) & "****Einde fnc CheckMatch ****" & Chr(13)

Exit Function

End:

CheckMatch = False

Debug.Print Chr(13) & "****End fnc CheckMatch ****" & Chr(13)

End Function



Extra info:

Combi_2Arg in my testing so far is the following string of text:

"0 30522".

This is, as the name of the variable Combi_2Arg suggests, a combination of
2 pieces of data. It is found on a sheet that has a table in which this
combination can be found. The name of this Boolean-function speaks for
itself.



This one doesn't work



Function LineReference_Check(Combi_3Arg As String) As Boolean

' This function tries to find the actual destination row on a certain
sheet of data

' from a different sheet by looking for a search argument. If its found,
the function = True

Debug.Print Chr(13) & "****Begin fnc LineReference_Check ****" & Chr(13)

On Error GoTo End

Dim FoundIt As String

FoundIt = Range("test"). _

Find(What:=Combi_3Arg, _

After:=ActiveCell, _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False). _

Address

Debug.Print " FoundIt = " & FoundIt

If FoundIt < "" Then LineReference_Check = True Else GoTo Eind

Debug.Print Chr(13) & "****End fnc LineReference_Check ****" & Chr(13)

Exit Function

End:

LineReference_Check = False

Debug.Print Chr(13) & "****End fnc LineReference_Check ****" & Chr(13)

End Function



Extra info:

Combi_3Arg in my testing so far is the following string of text:

"01 000000 30522".



This is, as the name of the variable Combi_3Arg suggests, a combination of
3 pieces of data. It is supposed to be found on a sheet that is exactly like
the sheet the data came from, except for the sheetname.



The strange thing is, that when I found this second function not to work, I
tested looking for the info (which is in fact on the sheet it is supposed to
be found on using the 2nd function) by recording a macro, I did find it.



Greetings,

Peter

After:=ActiveCell

Is the activecell in the range Test?

Can you count on that being true?

--
Regards,
Tom Ogilvy


"Peter" wrote in message
...
Hello everyone,



I have made two functions. Both of them use the find method, yet only one
of
them actually works and I can't find out why the other one doesn't.



Here is the one that works.



Function CheckMatch(Combi_2Arg As String) As Boolean

Debug.Print Chr(13) & "**** CheckMatch ****" & Chr(13)

On Error GoTo End

Dim FoundIt As String

FoundIt = Range("Locations").Find _

(What:=Combi_2Arg, _

After:=Range("LocationBegin"), _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False) _

.Address

Debug.Print " FoundIt = " & FoundIt

If FoundIt < "" Then CheckMatch = True Else GoTo End

Debug.Print Chr(13) & "****Einde fnc CheckMatch ****" & Chr(13)

Exit Function

End:

CheckMatch = False

Debug.Print Chr(13) & "****End fnc CheckMatch ****" & Chr(13)

End Function



Extra info:

Combi_2Arg in my testing so far is the following string of text:

"0 30522".

This is, as the name of the variable Combi_2Arg suggests, a combination
of
2 pieces of data. It is found on a sheet that has a table in which this
combination can be found. The name of this Boolean-function speaks for
itself.



This one doesn't work



Function LineReference_Check(Combi_3Arg As String) As Boolean

' This function tries to find the actual destination row on a certain
sheet of data

' from a different sheet by looking for a search argument. If its
found,
the function = True

Debug.Print Chr(13) & "****Begin fnc LineReference_Check ****" &
Chr(13)

On Error GoTo End

Dim FoundIt As String

FoundIt = Range("test"). _

Find(What:=Combi_3Arg, _

After:=ActiveCell, _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False). _

Address

Debug.Print " FoundIt = " & FoundIt

If FoundIt < "" Then LineReference_Check = True Else GoTo Eind

Debug.Print Chr(13) & "****End fnc LineReference_Check ****" & Chr(13)

Exit Function

End:

LineReference_Check = False

Debug.Print Chr(13) & "****End fnc LineReference_Check ****" & Chr(13)

End Function



Extra info:

Combi_3Arg in my testing so far is the following string of text:

"01 000000 30522".



This is, as the name of the variable Combi_3Arg suggests, a combination
of
3 pieces of data. It is supposed to be found on a sheet that is exactly
like
the sheet the data came from, except for the sheetname.



The strange thing is, that when I found this second function not to work,
I
tested looking for the info (which is in fact on the sheet it is supposed
to
be found on using the 2nd function) by recording a macro, I did find it.



Greetings,

Peter

Hi Peter from another Peter

Your second function worked fine for me, after making two small changes:
First I had to rename the lable "End" to [say] "EndFunc". Never a good idea
to name a lable the same as a vba keyword, particularly End. Also, by chance
I had a procedure in another file named "test", same as your defined range,
so I had to change that name.

FWIW, if an unknown problem occurs, the first thing to do is remove or
comment the error handler. Then step through and investigate the error
description.

Regards,
Peter

"Peter" wrote in message
...
Hello everyone,



I have made two functions. Both of them use the find method, yet only one
of
them actually works and I can't find out why the other one doesn't.



Here is the one that works.



Function CheckMatch(Combi_2Arg As String) As Boolean

Debug.Print Chr(13) & "**** CheckMatch ****" & Chr(13)

On Error GoTo End

Dim FoundIt As String

FoundIt = Range("Locations").Find _

(What:=Combi_2Arg, _

After:=Range("LocationBegin"), _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False) _

.Address

Debug.Print " FoundIt = " & FoundIt

If FoundIt < "" Then CheckMatch = True Else GoTo End

Debug.Print Chr(13) & "****Einde fnc CheckMatch ****" & Chr(13)

Exit Function

End:

CheckMatch = False

Debug.Print Chr(13) & "****End fnc CheckMatch ****" & Chr(13)

End Function



Extra info:

Combi_2Arg in my testing so far is the following string of text:

"0 30522".

This is, as the name of the variable Combi_2Arg suggests, a combination
of
2 pieces of data. It is found on a sheet that has a table in which this
combination can be found. The name of this Boolean-function speaks for
itself.



This one doesn't work



Function LineReference_Check(Combi_3Arg As String) As Boolean

' This function tries to find the actual destination row on a certain
sheet of data

' from a different sheet by looking for a search argument. If its
found,
the function = True

Debug.Print Chr(13) & "****Begin fnc LineReference_Check ****" &
Chr(13)

On Error GoTo End

Dim FoundIt As String

FoundIt = Range("test"). _

Find(What:=Combi_3Arg, _

After:=ActiveCell, _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False). _

Address

Debug.Print " FoundIt = " & FoundIt

If FoundIt < "" Then LineReference_Check = True Else GoTo Eind

Debug.Print Chr(13) & "****End fnc LineReference_Check ****" & Chr(13)

Exit Function

End:

LineReference_Check = False

Debug.Print Chr(13) & "****End fnc LineReference_Check ****" & Chr(13)

End Function



Extra info:

Combi_3Arg in my testing so far is the following string of text:

"01 000000 30522".



This is, as the name of the variable Combi_3Arg suggests, a combination
of
3 pieces of data. It is supposed to be found on a sheet that is exactly
like
the sheet the data came from, except for the sheetname.



The strange thing is, that when I found this second function not to work,
I
tested looking for the info (which is in fact on the sheet it is supposed
to
be found on using the 2nd function) by recording a macro, I did find it.



Greetings,

Peter