SOLVED

How do i restrict user from entering duplicate values for vendor's name?

Frequent Contributor

I would like to have a macro code to restrict users from entering a duplicate vendor name.  

 

I would like the code to loop back to request the users to re-enter a new value.

 

This is my code so far:

 

Sub BillingMilestone()

Dim FileName As String
Dim CountVendor As Integer
Dim Count As Integer
Dim contractbid As String
Dim sum1 As Long
Dim sum2 As Long
Dim sum3 As Long
Dim sum4 As Long
Dim ws As Worksheet
Dim deposit As String
Dim depositfig As String
Dim vendor As Range
Dim cel As Variant

Set ws = Sheet1

FileName = InputBox("Please indicate this file name")

If FileName = "" Then

MsgBox ("Macro exited. Click on the button to re-run.")
Exit Sub

End If

ws.Range("A1") = "File Name:"
ws.Range("B1") = FileName

CountVendor = 0
CountVendor = InputBox("Please indicate number of vendor(s)")

If CountVendor = 0 Then
MsgBox ("You have entered an invalid number. Please click on the clear contents button before re-executing the Macro again")
Exit Sub
 
Else

End If

ws.Range("A2").Value = "No. of Vendor(s)"
ws.Range("B2").Value = "Vendor(s) Name"
ws.Range("C2").Value = "Vendor(s) cost excl GST"
ws.Range("D2").Value = "Vendor(s) cost incl GST"
ws.Range("E2").Value = "Vendor(s) payment terms"

For Count = 1 To CountVendor
CountVendor = CountVendor + Count
ws.Range("A" & Count + 2).Value = Count

ws.Range("B" & Count + 2) = InputBox("Name of Vendor " & Count)

Set vendor = Range("B3:B" & Range("B" & Rows.Count).End(xlUp).row)

For Each cel In vendor '<--- identify duplicate vendor names

If Application.WorksheetFunction.CountIf(vendor, cel) > 1 Then

MsgBox ("You have entered a duplicate vendor name entry")

Else

End If

Next

ws.Range("C" & Count + 2) = InputBox("Please indicate Vendor " & Count & " amount in SGD (excl GST)")
ws.Range("C" & Count + 2).NumberFormat = "#,##0.00"

ws.Range("D" & Count + 2).Value = ws.Range("C" & Count + 2) * 1.07
ws.Range("D" & Count + 2).NumberFormat = "#,##0.00"

ws.Range("E" & Count + 2) = InputBox("Please indicate Payment Terms for Vendor" & Count & ".")

Next Count

sum1 = ws.Cells(ws.Rows.Count, "B").End(xlUp).row
sum2 = ws.Cells(ws.Rows.Count, "C").End(xlUp).row
sum3 = ws.Cells(ws.Rows.Count, "D").End(xlUp).row

ws.Range("A" & sum1 + 1).Value = "Total"

With ws.Range("C" & sum2 + 1)
        '.NumberFormat = "#,##0.00"
        .Formula = "=SUM(C3:C" & sum2 & ")"
        .Value = .Value
        
    End With

ws.Range("C3" & sum2).NumberFormat = "#,##0.00"

With ws.Range("D" & sum3 + 1)
        '.NumberFormat = "#,##0.00"
        .Formula = "=SUM(D3:D" & sum3 & ")"
        .Value = .Value
        
End With

ws.Range("D3" & sum2).NumberFormat = "#,##0.00"

deposit = InputBox("Deposit required for this tender? Please enter Yes or No.")

ws.Range("G1").Value = "Deposit Required:"

ws.Range("H1") = deposit
ws.Range("H1").HorizontalAlignment = xlRight

If UCase(ws.Range("H1")) = "Yes" Then

ws.Range("G2").Value = "% Deposit of the total contract sum:"

Else

End If

sum4 = ws.Cells(ws.Rows.Count, "G").End(xlUp).row

ws.Range("G" & sum4 + 1).Value = "Contract Bid (excl GST)"
ws.Range("G" & sum4 + 2).Value = "Contract Bid (incl GST)"

contractbid = InputBox("Please indicate contract bid (excl GST).")

ws.Range("G" & sum4 + 1).Offset(0, 1) = contractbid
ws.Range("G" & sum4 + 1).Offset(0, 1).NumberFormat = "#,##0.00"

ws.Range("G" & sum4 + 2).Offset(0, 1).Value = contractbid * 1.07
ws.Range("G" & sum4 + 2).Offset(0, 1).NumberFormat = "#,##0.00"
        
If UCase(ws.Range("H1")) = "Yes" Then

depositfig = InputBox("Please indicate deposit % of the total contract sum. For eg, 5% deposit, please enter as 5.")

ws.Range("H2").Value = (ws.Range("G" & sum4 + 2).Offset(0, 1) / 100) * depositfig

ws.Range("H2").NumberFormat = "#,##0.00"
          
Else

End If

Call contracttenure
Call findlastrow
Call amrtbilling
Call alignment

End Sub

 

 

Appreciate and thanks in advance!

 

2 Replies
best response confirmed by hrh_dash (Frequent Contributor)
Solution

@hrh_dash 

Please indent your code consistently, otherwise it is difficult to read.

If you have no code between Else and End If, you don't need Else.

I have removed some inconsistencies from the code, and inserted a question in a comment.

Sub BillingMilestone()
    Dim FileName As String
    Dim CountVendor As Integer
    Dim Count As Integer
    Dim contractbid As String
    Dim sum1 As Long
    Dim sum2 As Long
    Dim sum3 As Long
    Dim sum4 As Long
    Dim ws As Worksheet
    Dim deposit As String
    Dim depositfig As String
    Dim vendor As Range
    Dim cel As Variant
    Dim newVendor As String
    Dim f As Boolean

    Set ws = Sheet1

    FileName = InputBox("Please indicate this file name")
    If FileName = "" Then
        MsgBox ("Macro exited. Click on the button to re-run.")
        Exit Sub
    End If

    ws.Range("A1") = "File Name:"
    ws.Range("B1") = FileName

    CountVendor = InputBox("Please indicate number of vendor(s)")
    If CountVendor = 0 Then
        MsgBox ("You have entered an invalid number. Please click on the clear contents button before re-executing the Macro again")
        Exit Sub
    End If

    ws.Range("A2").Value = "No. of Vendor(s)"
    ws.Range("B2").Value = "Vendor(s) Name"
    ws.Range("C2").Value = "Vendor(s) cost excl GST"
    ws.Range("D2").Value = "Vendor(s) cost incl GST"
    ws.Range("E2").Value = "Vendor(s) payment terms"

    For Count = 1 To CountVendor
        CountVendor = CountVendor + Count
        ws.Range("A" & Count + 2).Value = Count
        f = False
        Do
            newVendor = InputBox("Name of Vendor " & Count)
            If newVendor <> "" Then
                Set vendor = ws.Range("B3:B" & ws.Range("B" & ws.Rows.Count).End(xlUp).Row)
                If Application.WorksheetFunction.CountIf(vendor, newVendor) > 0 Then
                    MsgBox "You have entered a duplicate vendor name entry"
                Else
                    f = True
                End If
            End If
        Loop Until f
        ws.Range("B" & Count + 2).Value = newVendor

        ws.Range("C" & Count + 2).Value = InputBox("Please indicate Vendor " & Count & " amount in SGD (excl GST)")
        ws.Range("C" & Count + 2).NumberFormat = "#,##0.00"
        ws.Range("D" & Count + 2).Value = ws.Range("C" & Count + 2) * 1.07
        ws.Range("D" & Count + 2).NumberFormat = "#,##0.00"
        ws.Range("E" & Count + 2).Value = InputBox("Please indicate Payment Terms for Vendor" & Count & ".")
    Next Count

    sum1 = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    sum2 = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    sum3 = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row

    ws.Range("A" & sum1 + 1).Value = "Total"

    With ws.Range("C" & sum2 + 1)
        '.NumberFormat = "#,##0.00"
        .Formula = "=SUM(C3:C" & sum2 & ")"
        .Value = .Value
    End With

    ws.Range("C3" & sum2).NumberFormat = "#,##0.00"

    With ws.Range("D" & sum3 + 1)
        '.NumberFormat = "#,##0.00"
        .Formula = "=SUM(D3:D" & sum3 & ")"
        .Value = .Value
    End With

    ' *** Shouldn't this use sum3 instead of sum2 ? ***
    ws.Range("D3" & sum2).NumberFormat = "#,##0.00"

    deposit = InputBox("Deposit required for this tender? Please enter Yes or No.")
    ws.Range("G1").Value = "Deposit Required:"
    ws.Range("H1").Value = deposit
    ws.Range("H1").HorizontalAlignment = xlRight

    If UCase(ws.Range("H1").Value) = "Yes" Then
        ws.Range("G2").Value = "% Deposit of the total contract sum:"
    End If

    sum4 = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row

    ws.Range("G" & sum4 + 1).Value = "Contract Bid (excl GST)"
    ws.Range("G" & sum4 + 2).Value = "Contract Bid (incl GST)"

    contractbid = InputBox("Please indicate contract bid (excl GST).")

    ws.Range("G" & sum4 + 1).Offset(0, 1).Value = contractbid
    ws.Range("G" & sum4 + 1).Offset(0, 1).NumberFormat = "#,##0.00"

    ws.Range("G" & sum4 + 2).Offset(0, 1).Value = contractbid * 1.07
    ws.Range("G" & sum4 + 2).Offset(0, 1).NumberFormat = "#,##0.00"
        
    If UCase(ws.Range("H1").Value) = "Yes" Then
        depositfig = InputBox("Please indicate deposit % of the total contract sum. For eg, 5% deposit, please enter as 5.")
        ws.Range("H2").Value = (ws.Range("G" & sum4 + 2).Offset(0, 1) / 100) * depositfig
        ws.Range("H2").NumberFormat = "#,##0.00"
    End If

    Call contracttenure
    Call findlastrow
    Call amrtbilling
    Call Alignment
End Sub

@Hans Vogelaar ;

Please indent your code consistently, otherwise it is difficult to read. -> noted on this, am working towards cleaning up my code so that whoever reads it will be much more easier.

 

If you have no code between Else and End If, you don't need Else. -> noted on this.

 

I have removed some inconsistencies from the code, and inserted a question in a comment. -> thank you for pointing out, it should be sum3 instead of sum2. 

 

Thank you for the solution and pointing out the mistakes. As always appreciate your help.