vba大神来看看

2018-07-20 07:37:34 595 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【Excel VBA 从入门到出门】二、Excel VBA 简单使用人们用 VBA 编写的大多数代码都是 `Sub` 和 `Function` 过程。因此，在本节中，我们将主要学习如何编写、编辑和运行它们。阅读详情

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

...全文

VBA代码 EXCEL宏入门阅读详情

xj8160

VBA 2,506 成员 10,830 内容

+ 关注 + 加入社区

切换为时间正序

舉杯邀明月 2018-11-28

打赏
举报

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

du鸡汤的一生 2018-11-27

打赏
举报

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

milaoshu1020 2018-11-04

打赏
举报

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