公式

panhua0624

Private Sub CommandButton1_Click()
'
'Application.ScreenUpdating = False
Dim ar As Variant
Dim br()
Dim rn As Range
With Sheets("造价")
    r = .Cells(Rows.Count, 1).End(xlUp).Row
    If r < 2 Then MsgBox "造价表为空！": End
    ar = .Range("a1:AA" & r)
End With
ReDim br(1 To UBound(ar), 1 To 8)
For i = 2 To UBound(ar)
    If Trim(ar(i, 1)) <> "" Then
        n = n + 1
        br(n, 1) = n
        br(n, 2) = ar(i, 2)
        br(n, 3) = ar(i, 3)
        br(n, 4) = ar(i, 8)
        br(n, 5) = ar(i, 4)
        br(n, 6) = ar(i, 7)
        br(n, 7) = br(n, 5) * br(n, 6)

    End If
Next i
With Sheets("报价单")
    rs = .Cells(Rows.Count, 6).End(xlUp).Row
    .Rows("5:" & rs - 1).Delete
    .Rows(5).Resize(n).Insert Shift:=xlDown
    .[a5].Resize(n, UBound(br, 2)) = br
    .[a5].Resize(n, UBound(br, 2)).Borders.LineStyle = 1
    .Cells(n + 5, 7).FormulaR1C1 = "=SUM(R[-" & n & "]C:R[-1]C)"
End With
End Sub

怎么把br(n, 7) 变成br(n, 5)单元格 * br(n, 6)单元格的公式

zjzyj

With Sheets("报价单")
    rs = .Cells(Rows.Count, 6).End(xlUp).Row
    .Rows("5:" & rs - 1).Delete
    .Rows(5).Resize(n).Insert Shift:=xlDown
    .[a5].Resize(n, UBound(br, 2)) = br

    .[G5].Resize(n) = "=E5+F5"

    .[a5].Resize(n, UBound(br, 2)).Borders.LineStyle = 1
    .Cells(n + 5, 7).FormulaR1C1 = "=SUM(R[-" & n & "]C:R[-1]C)"
End With

盼哥

看下是不是你想要的？

panhua0624

盼哥 发表于 2024-4-5 17:30
看下是不是你想要的？

对的，谢谢大侠