Jul 26 2022 01:07 AM - edited Jul 26 2022 01:08 AM
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!
Jul 26 2022 03:35 AM
SolutionPlease 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
Jul 26 2022 04:13 AM
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.
Jul 26 2022 03:35 AM
SolutionPlease 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