Validating 2 cells goes wrong, finds first match instead of exact match.

Hi All,

I'm new here. In advance, already thanks for helping/thinking with me :-).

Situation:
- I've a sheet with data input.
- I've a validation button.
- I've a sheet with valid information (containing company names).
- I've a sheet with validated information (output).

My problem is the following:
I put information on the data input sheet, after the validation button, it comes up with the wrong company name.

Example:
There's a company called BN, but after pressing the validation button, the VBA checks the sheet with valid information and finds ABN as the first option and stops looking for BN, then ABN (instead of BN) gets put on the validated information sheet.

I would like:
Check the whole cell/whole text (has to match entirely) instead of find the first.

The current code:
Set lookup = Sheets(SHEET_CP_NAME).Range("CP_name")

I think it would be something like this, but unfortunately that doesn't work:
Set lookup = Sheets(SHEET_CP_NAME).Range("CP_name").Find(LookAt:=xlWhole)

Thanks again for your help and effort.

Hi TMS,

Sorry, I can't post the whole workbook. It's 8MB of size.

The only problem which occurs happens in this code (here's the entire code) of the module:

' If found return true, else false.
Private Function ValidateComposedAssetLevel(ByRef aCell1 As Range, ByRef aCell2, ByRef ValidationMsg As String) As Boolean
Dim lookup As Range
Dim composedAssetCell As Range

' Check for empty string first-->
If LenB(aCell1.value) = 0 Or LenB(aCell2.value) = 0 Then
ValidationMsg = "Empty asset level 1 or 2."
ValidateComposedAssetLevel = False
Exit Function
End If


' Temporary location -->
Set composedAssetCell = Sheets("Admin").Range("$A$60")

' Value to validate is the two asset levels combined -->
composedAssetCell.value = aCell1.value & aCell2.value

Set lookup = Sheets(SHEET_ASSET_LEVELS_1_2).Range("AL1_AL2")

If ValidateToLookup(composedAssetCell, lookup) = False Then
ValidationMsg = "Invalid combined asset level (not in list)."
ValidateComposedAssetLevel = False
Else
ValidationMsg = vbNullString
ValidateComposedAssetLevel = True
End If

' Clear temporary location -->
composedAssetCell.value = ""

End Function

'
' Validate a CP name to a lookup list.
' Function returns true when found, false otherwise.
Private Function ValidateCpName(ByRef aCell As Range, ByRef ValidationMsg As String) As Boolean
Dim lookup As Range

If AutoFillCpName = True And Sheets(SHEET_TEMPLATE).Range("$D$" & CStr(aCell.Row)).value = "Y" Then
aCell.value = IC_NAME
ValidationMsg = vbNullString
ValidateCpName = True
Exit Function
End If

If Sheets(SHEET_TEMPLATE).Range("$D$" & CStr(aCell.Row)).value = "Y" And Left(aCell.value, 3) <> "IC-" Then
ValidationMsg = "IC items should have a CP name that starts with 'IC-'."
ValidateCpName = False
Exit Function
End If



' Check for empty string first-->
If LenB(aCell.value) = 0 Then
ValidationMsg = "Empty CP name."
ValidateCpName = False
Exit Function
End If

Set lookup = Sheets(SHEET_CP_NAME).Range("CP_name")

If ValidateToLookup(aCell, lookup) = False Then
ValidationMsg = "Invalid name (not in list)."
ValidateCpName = False
Else
ValidationMsg = vbNullString
ValidateCpName = True
End If
End Function