Clear a cell if duplicate VBA

Caveman1964

Board Regular
Joined
Dec 14, 2017
Messages
127
Office Version
  1. 2016
Platform
  1. Windows
Please forgive my stupidity......I am trying to put forth the effort.
Ok, I got this code to find a duplicate on another page, but what I have written doesnt work to clear the cell after it learned it was a duplicate.

hear is my novice code.

Sub askcompnum()


Title = InputBox("Enter a Complaint Number", "LSS Complaint Tracking System")
Range("E5") = Title


If WorksheetFunction.CountIf(Worksheets("Data Collection").Columns(1), Worksheets("Complaint Entry").Range("E5")) Then
MsgBox "Duplicate, You cannot use"

If MsgBox = "Duplicate, you cannot use" Then
Range("E5").Select
ActiveCell.Clear

Else

End If
End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
try this:

Code:
Title = InputBox("Enter a Complaint Number", "LSS Complaint Tracking System")

If IsNumeric(Title) Then
    mtch = Application.Match(Title, Worksheets("Data Collection").Columns(1), 0)
    If IsError(mtch) Then mtch = Application.Match(CDbl(Title), Worksheets("Data Collection").Columns(1), 0)
Else
    mtch = Application.Match(Title, Worksheets("Data Collection").Columns(1), 0)
End If

If Not IsError(mtch) Then
    MsgBox "Duplicate, You cannot use"
    Worksheets("Complaint Entry").Range("E5").Value = vbNullString
Else
    Worksheets("Complaint Entry").Range("E5").Value = Title
End If
 
Upvote 0
Code:
Sub askcompnum()
    Dim Title
    Dim Duplicate As Boolean


    Title = InputBox("Enter a Complaint Number", "LSS Complaint Tracking System")
    Range("E5").Value = Title


    Duplicate = (WorksheetFunction.CountIf(Worksheets("Data Collection").Columns(1), Worksheets("Complaint Entry").Range("E5")) > 0)
    If Duplicate Then
        MsgBox Range("E5") & " is duplicate LSS complaint tracking number" & vbNewLine & vbNewLine & "You cannot use it", vbExclamation, "Data Validation Check"
        Range("E5").ClearContents
    End If
End Sub
 
Upvote 0
Hi there,

Try this where the entry in the inputbox won't be returned to cell E5 of the Complaint Entry tab if it already exists in column A of the Data Collection tab:

Code:
Option Explicit
Sub Macro1()

    Dim varResponse As Variant
    
    varResponse = InputBox("Enter a Complaint Number", "LSS Complaint Tracking System")
    
    If varResponse <> "" Then 'Will = "" if the <Cancel> button was clicked
        If WorksheetFunction.CountIf(Worksheets("Data Collection").Columns("A"), varResponse) >= 1 Then
            MsgBox varResponse & " already exists and therefore cannot be used again.", vbExclamation
        Else
            Sheets("Complaint Entry").Range("E5").Value = varResponse
        End If
    End If

End Sub

Regards,

Robert
 
Upvote 0

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top