lixiaoming980
New Member
- Joined
- Jun 18, 2023
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
VBA Code:
Sub delivery()
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
If Range("e3") = "" Then Exit Sub
If Range("e4") = "" Then Exit Sub
If Range("e5") = "" Then Exit Sub
If Range("e6") = "" Then Exit Sub
If Range("e7") = "" Then Exit Sub
If Range("e8") = "" Then Exit Sub
If Range("e11") = "" Then
MsgBox "Please input supplier or contacter name"
Exit Sub
End If
If Range("e2") = "New Item" Then
rownum = Range("a1048576").End(xlUp).Row + 1
Range("a" & rownum) = rownum - 13
Else
rownum = Range("a14:a1048576").Find([e2]).Row
End If
Range("b" & rownum) = Range("e3")
Range("h" & rownum) = Range("e4")
Range("c" & rownum).Value = UCase(Range("e5"))
Range("d" & rownum) = Range("e6")
Range("e" & rownum) = Range("e7")
Range("f" & rownum) = Range("e8")
Range("g" & rownum) = Abs(Range("e10"))
Range("m" & rownum) = UCase(Range("e11"))
Range("J" & rownum) = "=I" & rownum & "*g" & rownum
ActiveWorkbook.RefreshAll
Call clear
If Range("S" & rownum) >= 2 Then
MsgBox "Probably that you made it twice or more, check it please. "
End If
If Range("H" & rownum) = "OUT" Then
Range("I" & rownum) = "=VLOOKUP(D" & rownum & ",Inventory!A:H,7,0)"
End If
End Sub
VBA Code:
Sub delete()
If IsNumeric([e2]) Then
Beep
If MsgBox("Are you sure to erase it?", vbYesNo) = vbNo Then Exit Sub
rownum = Range("a12:a1048576").Find([e2]).Row
Rows(rownum).delete
Range("e2") = "New Item"
[e3:e11] = ""
End If
If rownum = "" Then Exit Sub
rowmax = Range("a1048576").End(xlUp).Row
x = rownum - 13
If x = 1 Then
Range("a14").Value = ""
Exit Sub
End If
If rowmax >= rownum Then
For i = rownum To rowmax
Range("a" & i).Value = x
x = x + 1
Next
End If
ActiveWorkbook.RefreshAll
End Sub
HI, everyone, Iam a newbie of vba.. I need to speed up this code above, everytime when I run the macro "delivery()" and "delete", it has to take a ten seconds to finish that.. can somebody help me out , thanks in advance.