vba大神来看看

[xj8160]

想编个开发票，一张最多116000，所以里面的金额要拆开好几张。要求吨位保留三位小数以内，如【116000/4480（结算价）=25.892XXX(就取三位小数)然后倒回去算出25.892*4480=115996.16（这个就是开票金额）】开的金额要接近116000但不能超过这个数字以此类推。然后新建到另一个表里，另一张需体现【物质名称 规格型号 单位 数量（25.892） 结算单价（4480）对应金额（115996.16）】VBA小白，就是知道能做，量大麻烦，谢谢大神。

[舉杯邀明月]

都有“想法”了，按你的想法写代码实现就行了呗。


那个“3位小数”，可以设置“单元格格式”来显示的，
　单元格中的“实际值”建议还是保留它的原始值，这样在“统计”的时候，可以减小“误差”。

[xj8160]

问题是写不来呀，完全小白一个，现在在学习中，希望有个例子自己边学边研究

[dragonrabb]

可以直接用公式呀……

[milaoshu1020]

请叫我雷锋!
打开宏对话框,新建一个名称为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

[du鸡汤的一生]

你看看这个 我试了可以的 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

[舉杯邀明月]

楼主都失踪了，还在来挖坟…………