C1-a | |
C1-b | |
C1 | prevent |
C2 | |
C2-a | prevent |
<tbody>
</tbody>
Hi,
I found many posts that have solution to prevent entering duplicate values in a column's cell.
I am trying to solve the same problem which has little different flavor.
In above table I want to stop users if they try to enter the same value (before "-").
E.G. A1-a if already there, A1 should not be allowed.
Essentially, the logic should first...
run this formula "=LEFT(C1,FIND("-",C1)-1)"
then run the list validation using countif.
I am using the folloiwng line of vb code but don't know how do i add the LEFT formula component to it or update the code to prevent duplicate comparing string before "-" i.e. dash.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CountIf(Range("a:a"), Target) > 1 Then
MsgBox "Duplicate....", vbCritical, "Can't take dups :("
'what this should do after the error msg is closed
Target.Value = ""
End If
End Sub