此代碼的目標是接受用戶輸入的數字 (New_Project_Number)，然后讀取一個串列（A3 列到該列的最后一行）比較數字并檢查重復項。然后將 New_Project_Number 粘貼到“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
但是，這會檢查是否有欺騙行為，但不會將代碼發布到底部。如果我添加
Else wss.range("A3").end(Xldown).offset(1,0)
在 then 陳述句之后和之前
Next ii
然后出現錯誤資訊“else without if statement”
如何修復代碼以便檢查所有使用的專案編號，然后在最后一個單元格上寫入新專案編號。現在這只檢查是否有欺騙性。
uj5u.com熱心網友回復：
使用Match()會更快，無需回圈：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/452974.html
下一篇：如何知道組合框是否有專案？