Add user entry if not a duplicate

Question

The goal of this code is to accept a user input number (New_Project_Number) then read down a list (column A3 to last row of the column) compare the numbers and check for duplicates. Then paste the New_Project_Number to the last row of column "A".

Sub Project_Number_Standerdization()

Dim New_Project_Number As Variant
Dim Used_Project_Number As Variant
Dim Last_Pn As Integer 'this is a looping variable for the last row in column a
Dim wss As Worksheet
Dim ii As Integer

New_Project_Number = Application.InputBox("What is the New Project Number?", Type:=1)
Set wss = ActiveSheet
Last_Pn = wss.Range("A3").End(xlDown)


For ii = 1 To Last_Pn

Used_Project_Number = wss.Range("A3").Offset(ii - 1, 0).Value

If New_Project_Number = Used_Project_Number _
Then MsgBox ("That project number is being used please choose a different one.") _
Next ii 

End Sub

This checks for dupes however will not post the code to the bottom. If I add

Else wss.range("A3").end(Xldown).offset(1,0)

right after the then statement and right before

Next ii

Then an error message appears

"else without if statement"

How can I check all used Project Numbers then write the New project number on the last cell. Right now this only checks for dupes.

This [link](https://stackoverflow.com/a/9415163/18620048) might be useful. — L0ndl3m, Mar 30 '22 at 17:08
Because you have a line continuation symbol `_` at the end of the `MsgBox` statement, VBA thinks the `Next` line is a part of it. Remove the `_` and you should be `good. — PeterT, Mar 30 '22 at 17:09
Also you really want to read up on multi-line `If...End If` syntax. — BigBen, Mar 30 '22 at 17:10
Thank you all for these suggestions. What a great community! — NotACoderNoah, Mar 30 '22 at 17:55

Tim Williams · Accepted Answer · 2022-03-30T17:46:53.033

2

Using Match() would be faster and no need to loop:

Sub ProjectNumberStandardization()

    Dim New_Project_Number As Variant
    Dim m As Variant
    Dim wss As Worksheet
    
    Set wss = ActiveSheet
    
    New_Project_Number = Application.InputBox("What is the New Project Number?", Type:=1)
    m = Application.Match(New_Project_Number, wss.Columns("A"), 0)
    
    If IsError(m) Then 'no existing match?
        'add the number to the next empty cell at the bottom (xlUp is safer than xlDown)
        wss.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = New_Project_Number
    Else
        MsgBox "That project number is being used please choose a different one."
    End If
    
End Sub

edited Mar 30 '22 at 17:46

answered Mar 30 '22 at 17:19

Tim Williams

154,628
8
97
125

@TimWilliams OT: What would be your word on using this approach instead of declaring a range variable and check if the range is nothing? – Sgdva Mar 30 '22 at 18:47
1

@Sgdva - do you mean using `Find()` instead of `Match()`? Match is typically much faster, so I'd default to that unless I needed some of the additional flexibility available from `Find()` – Tim Williams Mar 30 '22 at 19:06
@TimWilliams Thanks: in this regard, if match gives a number, wouldn't be better to declarate it as long, set "on error resume next" and if this 'm' variable is 0 means there is no existing match too? – Sgdva Mar 30 '22 at 19:14
1

@Sgdva - that's another approach, Yes, but why not take advantage of the fact that it doesn't raise a run-time error when you assign the return to a Variant? Trapping an error is more work... – Tim Williams Mar 30 '22 at 20:42

Add user entry if not a duplicate

1 Answers1