想編個開發票，一張最多116000，所以里面的金額要拆開好幾張。要求噸位保留三位小數以內，如【116000/4480（結算價）=25.892XXX(就取三位小數)然后倒回去算出25.892*4480=115996.16（這個就是開票金額）】開的金額要接近116000但不能超過這個數字以此類推。然后新建到另一個表里，另一張需體現【物質名稱 規格型號 單位 數量（25.892） 結算單價（4480）對應金額（115996.16）】VBA小白，就是知道能做，量大麻煩，謝謝大神。
uj5u.com熱心網友回復：
都有“想法”了，按你的想法寫代碼實作就行了唄。
那個“3位小數”，可以設定“單元格格式”來顯示的，
單元格中的“實際值”建議還是保留它的原始值，這樣在“統計”的時候，可以減小“誤差”。
uj5u.com熱心網友回復：
問題是寫不來呀，完全小白一個，現在在學習中，希望有個例子自己邊學邊研究uj5u.com熱心網友回復：
可以直接用公式呀……uj5u.com熱心網友回復：
請叫我雷鋒!打開宏對話框,新建一個名稱為test的宏,然后點擊編輯,輸入如下代碼:
Sub test()
Sheet2.Cells(1, 1) = "物資名稱"
Sheet2.Cells(1, 2) = "規格型號"
Sheet2.Cells(1, 3) = "單位"
Sheet2.Cells(1, 4) = "數量"
Sheet2.Cells(1, 5) = "結算單價"
Sheet2.Cells(1, 6) = "對應金額"
Dim i As Integer
i = 2
While Sheet1.Cells(i, 1) <> ""
Dim j As Integer
For j = 1 To 7
If j <= 4 Then
Sheet2.Cells(i, j) = Sheet1.Cells(i, j)
ElseIf j = 7 Then
Sheet2.Cells(i, 5) = Sheet1.Cells(i, j)
End If
Next
If Sheet1.Cells(i, 8) > 116000 Then
Sheet2.Cells(i, 4) = Fix(116000 / Sheet2.Cells(i, 5) * 1000) / 1000
Sheet2.Cells(i, 6) = Sheet2.Cells(i, 4) * Sheet2.Cells(i, 5)
Else
Sheet2.Cells(i, 6) = Sheet1.Cells(i, 8)
End If
i = i + 1
Wend
End Sub
excel表格下載地址:
https://download.csdn.net/download/milaoshu1020/10763168
uj5u.com熱心網友回復：
你看看這個 我試了可以的Option Explicit
Sub 自動生成發票單()
Dim w As Worksheet
Dim w1 As Worksheet
Dim j As Integer
Dim i As Integer
Dim k As Integer
Dim x As Integer
Dim x1 As Double
Dim x2 As Double
k = 2
Set w = Worksheets("資料表")
Sheets.Add(before:=Sheets("資料表")).Name = "發票單"
Set w1 = Worksheets("發票單")
w1.Cells(1, 1) = w.Cells(1, 1)
w1.Cells(1, 2) = w.Cells(1, 2)
w1.Cells(1, 3) = w.Cells(1, 3)
w1.Cells(1, 4) = w.Cells(1, 4)
w1.Cells(1, 5) = w.Cells(1, 7)
w1.Cells(1, 6) = w.Cells(1, 8)
For j = 2 To w.UsedRange.Rows.Count Step 1
x = Round(w.Cells(j, 8) / 116000 + 0.5, 0)
If x > 1 Then
For i = 1 To x - 1 Step 1
x1 = Round(116000 / w.Cells(j, 7), 3) - 0.001
x2 = Round(x1 * w.Cells(j, 7), 2)
w1.Cells(k, 1) = w.Cells(j, 1)
w1.Cells(k, 2) = w.Cells(j, 2)
w1.Cells(k, 3) = w.Cells(j, 3)
w1.Cells(k, 4) = x1
w1.Cells(k, 5) = w.Cells(j, 7)
w1.Cells(k, 6) = x2
k = k + 1
x = x - 1
If x = 1 Then
w1.Cells(k, 1) = w.Cells(j, 1)
w1.Cells(k, 2) = w.Cells(j, 2)
w1.Cells(k, 3) = w.Cells(j, 3)
w1.Cells(k, 4) = w.Cells(j, 4) - (Round(116000 / w.Cells(j, 7), 3) - 0.001) * (Round(w.Cells(j, 8) / 116000 + 0.5, 0) - 1)
w1.Cells(k, 5) = w.Cells(j, 7)
w1.Cells(k, 6) = w.Cells(j, 8) - Round(x1 * w.Cells(j, 7), 2) * (Round(w.Cells(j, 8) / 116000 + 0.5, 0) - 1)
k = k + 1
End If
Next i
End If
Next j
End Sub
uj5u.com熱心網友回復：
樓主都失蹤了，還在來挖墳…………
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/42076.html
標籤：VBA
上一篇：SetupFactory 9.0打包后安裝出現注冊表錯誤
下一篇：pc機與PLC實作通信的問題