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.

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
– PeterT, Commented Mar 30, 2022 at 17:09
Also you really want to read up on multi-line If...End If syntax. — BigBen
– BigBen, Commented Mar 30, 2022 at 17:10
Thank you all for these suggestions. What a great community! — NotACoderNoah
– NotACoderNoah, Commented Mar 30, 2022 at 17:55

Tim Williams · Accepted Answer · 2022-03-30 17:46:53Z

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, 2022 at 17:46

answered Mar 30, 2022 at 17:19

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sgdva Over a year ago

@TimWilliams OT: What would be your word on using this approach instead of declaring a range variable and check if the range is nothing?

Tim Williams Over a year ago

@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()

Sgdva Over a year ago

@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?

Tim Williams Over a year ago

@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...

Collectives™ on Stack Overflow

Add user entry if not a duplicate

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related