P
Peter
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
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