|
发表于 2018-1-7 08:59
来自手机
|
显示全部楼层
Range("Q20").Value‘这段数据只需保存一次,不随其它数据行保存N次’
本帖最后由 gumption83 于 2018-1-7 10:55 编辑
Sub 保存()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim irowa, tq, mm, ll As Integer
Dim i, o As Integer
If Range("C4") = "" Then MsgBox "请输入商户名": Exit Sub
If Range("Q4") = "" Then MsgBox "请输入结款方式": Exit Sub
If Range("Q3") = "" Then MsgBox "请输入运输方式": Exit Sub
For i = 6 To Application.CountA(Range("A6:A17")) + 5
If Range("Q" & i) = "" Then MsgBox "请检查,有单价未填!": Exit Sub
Next
i = Sheet6.[F65536].End(xlUp).Row
For o = 2 To i
If Range("P1").Value = Sheet6.Range("F" & o).Value Then
ms = MsgBox("已经有了单据" & Range("P1").Value & "的信息,是否录入?", vbYesNo, "提示")
If ms = vbNo Then
Exit Sub
End If
End If
Next
With Sheet6
irowa = .Range("F65536").End(xlUp).Row
.Unprotect 123
For tq = 6 To Application.CountA(Range("A6:A17")) + 5
irowa = irowa + 1
.Range("G" & irowa & ":U" & irowa) = Range("A" & tq & ":O" & tq).Value
.Range("A" & irowa) = Right(Application.WorksheetFunction.Substitute([E1], " ", ""), 3)
.Range("B" & irowa) = Range("F21").Value
.Range("C" & irowa) = Range("Q2").Value
.Range("D" & irowa) = Range("C4").Value
.Range("E" & irowa) = Range("K4").Value
.Range("F" & irowa) = Range("P1").Value
.Range("AB" & irowa) = Range("Q4").Value
.Range("AD" & irowa) = Range("Q20").Value‘这段数据只需保存一次,不跟随其它数据行保存N次’
.Range("AE" & irowa) = Range("B21").Value
.Range("AF" & irowa) = Range("Q3").Value
If .Range("A" & irowa) = "入库单" Then
.Range("V" & irowa & ":X" & irowa) = Range("P" & tq & ":R" & tq).Value
Else
.Range("Y" & irowa & ":AA" & irowa) = Range("P" & tq & ":R" & tq).Value
End If
Next
End With
Application.EnableEvents = False
If Range("E1") = "入 库 单" Then
Range("V1") = Left(Range("P1"), 8) & Format(Right(Range("P1"), 4) + 1, "0000")
Range("P1") = Range("V1")
Range("A6:O17").ClearContents
Range("Q6:Q17").Value = ""
Else
Range("V2") = Left(Range("P1"), 8) & Format(Right(Range("P1"), 4) + 1, "0000")
Range("P1") = Range("V2")
Range("A6:O17").ClearContents
Range("Q6:Q17").Value = ""
End If
Range("C4,B18,Q3,Q20").Value = ""
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub |
|