英文:
Create new cell spaces in record sheet every new order
问题
我正在尝试每次将已保存的发票保存到记录表格时创建单元格间隔。
但是最后一项和数量的单元格会被保存覆盖。
如果我尝试从个别表格中进行排序，它不会作为一个完整的订单。
我正在尝试创建类似于以下的东西：
完整的代码如下：
Sub Save()
Dim SimpanInvoice As Object
Set SimpanInvoice = Sheet3.Range("A20000").End(xlUp)
If Sheet4.Range("Invonomer").Value = "" _
Or Sheet4.Range("Invotgl").Value = "" _
Or Sheet4.Range("Invoto").Value = "" _
Or Sheet4.Range("Invoalamat").Value = "" Then
Call MsgBox("请填写完整的数据", vbInformation, "填写数据")
Else
SimpanInvoice.Offset(1, 0).Value = Sheet4.Range("Invonomer").Value
SimpanInvoice.Offset(1, 1).Value = Sheet4.Range("Invotgl").Value
SimpanInvoice.Offset(1, 2).Value = Sheet4.Range("Invoto").Value
SimpanInvoice.Offset(1, 3).Value = Sheet4.Range("Invoalamat").Value
SimpanInvoice.Offset(1, 4).Value = Sheet4.Range("Keterangan").Value
SimpanInvoice.Offset(1, 5).Value = Sheet4.Range("item1").Value
SimpanInvoice.Offset(1, 6).Value = Sheet4.Range("Qty_1").Value
SimpanInvoice.Offset(2, 5).Value = Sheet4.Range("item2").Value
SimpanInvoice.Offset(2, 6).Value = Sheet4.Range("Qty_2").Value
Call MsgBox("发票数据已保存", vbInformation, "发票数据")
Sheet4.Range("Invonomer").Value = ""
Sheet4.Range("Invotgl").Value = ""
Sheet4.Range("Invoto").Value = ""
Sheet4.Range("Invoalamat").Value = ""
Sheet4.Range("Keterangan").Value = ""
Sheet4.Range("item1").Value = ""
Sheet4.Range("Qty_1").Value = ""
Sheet4.Range("item2").Value = ""
Sheet4.Range("Qty_2").Value = ""
End If
End Sub
英文:
I am trying to make cell spaces every time Saved Invoices go into record sheet.
Instead the last cell of item and qty get overwritten by the save.
And it didn't go as a whole one order if I try to sort it from individual table.
I am trying to make something like this:
The full code
Sub Save()
Dim SimpanInvoice As Object
Set SimpanInvoice = Sheet3.Range("A20000").End(xlUp)
If Sheet4.Range("Invonomer").Value = "" _
Or Sheet4.Range("Invotgl").Value = "" _
Or Sheet4.Range("Invoto").Value = "" _
Or Sheet4.Range("Invoalamat").Value = "" Then
Call MsgBox("Harap isi data dengan lengkap", vbInformation, "Isi Data")
Else
SimpanInvoice.Offset(1, 0).Value = Sheet4.Range("Invonomer").Value
SimpanInvoice.Offset(1, 1).Value = Sheet4.Range("Invotgl").Value
SimpanInvoice.Offset(1, 2).Value = Sheet4.Range("Invoto").Value
SimpanInvoice.Offset(1, 3).Value = Sheet4.Range("Invoalamat").Value
SimpanInvoice.Offset(1, 4).Value = Sheet4.Range("Keterangan").Value
SimpanInvoice.Offset(1, 5).Value = Sheet4.Range("item1").Value
SimpanInvoice.Offset(1, 6).Value = Sheet4.Range("Qty_1").Value
SimpanInvoice.Offset(2, 5).Value = Sheet4.Range("item2").Value
SimpanInvoice.Offset(2, 6).Value = Sheet4.Range("Qty_2").Value
Call MsgBox("Data SuratTagihan Telah Di Simpan", vbInformation, "Data Surat Tagihan")
Sheet4.Range("Invonomer").Value = ""
Sheet4.Range("Invotgl").Value = ""
Sheet4.Range("Invoto").Value = ""
Sheet4.Range("Invoalamat").Value = ""
Sheet4.Range("Keterangan").Value = ""
Sheet4.Range("item1").Value = ""
Sheet4.Range("Qty_1").Value = ""
Sheet4.Range("item2").Value = ""
Sheet4.Range("Qty_2").Value = ""
End If
End Sub
答案1
得分: 0
您的ColA未完全填充，因此您可以添加一个循环以确保您正在处理的行为空。
Dim SimpanInvoice As Object
Set SimpanInvoice = Sheet3.Range("A20000").End(xlUp).Offset(1)
'确保我们所在的行完全为空（不仅仅是ColA为空）
Do While Application.CountA(SimpanInvoice.EntireRow) > 0
Set SimpanInvoice = SimpanInvoice.Offset(1) '下一行
Loop
'开始填充行
SimpanInvoice.Value = Sheet4.Range("Invonomer").Value
SimpanInvoice.Offset(0, 1).Value = Sheet4.Range("Invotgl").Value
'等等
英文:
Your ColA is not fully populated, so you could add a loop to make sure the row you're working on is empty
Dim SimpanInvoice As Object
Set SimpanInvoice = Sheet3.Range("A20000").End(xlUp).Offset(1)
'make sure the row we're on is completely empty (not just empty in ColA)
Do While Application.CountA(SimpanInvoice.EntireRow) > 0
Set SimpanInvoice = SimpanInvoice.Offset(1) 'next row down
Loop
'start populating the row
SimpanInvoice.Value = Sheet4.Range("Invonomer").Value
SimpanInvoice.Offset(0, 1).Value = Sheet4.Range("Invotgl").Value
'etc etc
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论