[求助]新做了一个《物料出入汇总表》，请高手帮忙指正公式错误；

新做了一个《物料出入库表》，其中《总表》中“出库”一列的公式存在错误，请高手帮忙指正公式错误之处；谢谢

问题已由czzqb大侠帮忙解决，谢谢各位；

[此贴子已经被作者于2007-10-29 16:34:24编辑过]

附件

YfS0HsHs.rar(129.69 KB)

2007-10-29 13:54, 下载次数: 97

[求助]新做了一个《物料出入汇总表》，请高手帮忙指正公式错误；

比较一下“入库”列和“出库”列，前面是有{ }这个符号的，说明你按了三键，而后面出库列没有{ }号，说明没有按三键。

修改很简单：

点击H5，在编辑栏理按下三键，向下拉就可以了。

 Ctrl+Shift+Enter

非常感谢指正，谢谢；

Private Sub CommandButton11_Click()
    Dim s%, i%, j%, m%, n%, rng, arr(), Rdate As Date
    '[a5:i16].ClearContents
    Application.ScreenUpdating = False
    With Sheet5
        .UsedRange.Offset(4, 0).Clear
        Rdate = .[g3]
    End With
    Set ds = CreateObject("scripting.dictionary")
    a = Array("入库清单", "出库清单")
    For s = 0 To UBound(a)
        With Sheets(a(s))
            n = .[b65536].End(xlUp).Row
            rng = .Range("b2:i" & n)
        End With
        For i = 1 To UBound(rng)
            If rng(i, 2) <> "" And rng(i, 7) = Rdate Then
                k = rng(i, 2) & rng(i, 3)
                If Not ds.exists(k) Then
                    m = m + 1
                    ds.Add k, m
                    ReDim Preserve arr(1 To 8, 1 To m)
                    arr(1, m) = rng(i, 2)
                    arr(2, m) = rng(i, 3)
                    arr(3, m) = rng(i, 6)
                    arr(4, m) = rng(i, 5)
                    If a(s) = "入库清单" Then
                        arr(6, m) = rng(i, 4)
                    ElseIf a(s) = "出库清单" Then
                        arr(7, m) = rng(i, 4)
                    End If
                    arr(8, m) = arr(6, m) + arr(7, m)
                Else
                    If a(s) = "入库清单" Then
                        arr(6, ds(k)) = arr(6, ds(k)) + rng(i, 4)
                    ElseIf a(s) = "出库清单" Then
                        arr(7, ds(k)) = arr(7, ds(k)) + rng(i, 4)
                    End If
                    arr(8, ds(k)) = arr(6, ds(k)) + arr(7, ds(k))
                End If
            End If
        Next i
    Next s

    With Sheet5
        [b5].Resize(m, 8) = Application.Transpose(arr)
        .UsedRange.Font.Name = "Tahoma"
        .UsedRange.Font.Size = 10
        .Cells.EntireRow.AutoFit
        .Cells.EntireColumn.AutoFit
    End With
    MsgBox ("OK")
    Application.ScreenUpdating = True

End Sub
代码优化

Private Sub CommandButton1_Click()
    Dim s%, i%, j%, m%, n%, rng, arr(), Rdate As Date
    '[a5:i16].ClearContents
    Application.ScreenUpdating = False
    With Sheet5
        .UsedRange.Offset(4, 0).Clear
        Rdate = .[g3]
    End With
    Set ds = CreateObject("scripting.dictionary")
    a = Array("入库清单", "出库清单")
    For s = 0 To UBound(a)
        With Sheets(a(s))
            n = .[b65536].End(xlUp).Row
            rng = .Range("b2:i" & n)
        End With
        For i = 1 To UBound(rng)
            If rng(i, 2) <> "" And rng(i, 7) = Rdate Then
                k = rng(i, 2) & rng(i, 3)
                If Not ds.exists(k) Then
                    m = m + 1
                    ds.Add k, m
                    ReDim Preserve arr(1 To 8, 1 To m)
                    arr(1, m) = rng(i, 2)
                    arr(2, m) = rng(i, 3)
                    arr(3, m) = rng(i, 6)
                    arr(4, m) = rng(i, 5)
                    If a(s) = "入库清单" Then
                        arr(6, m) = rng(i, 4)
                    ElseIf a(s) = "出库清单" Then
                        arr(7, m) = rng(i, 4)
                    End If
                    arr(8, m) = arr(6, m) + arr(7, m)
                Else
                    If a(s) = "入库清单" Then
                        arr(6, ds(k)) = arr(6, ds(k)) + rng(i, 4)
                    ElseIf a(s) = "出库清单" Then
                        arr(7, ds(k)) = arr(7, ds(k)) + rng(i, 4)
                    End If
                    arr(8, ds(k)) = arr(6, ds(k)) - arr(7, ds(k))
                End If
            End If
        Next i
    Next s

    With Sheet5
        [b5].Resize(m, 8) = Application.Transpose(arr)
        n = .[b65536].End(xlUp).Row
        .Range("G2").Formula = "=SUM(G5:G" & n & ")"
        .Range("h2").Formula = "=SUM(h5:h" & n & ")"
        .Range("i2").Formula = "=SUM(i5:i" & n & ")"
        .UsedRange.Font.Name = "Tahoma"
        .UsedRange.Font.Size = 10
        .Cells.EntireRow.AutoFit
        .Cells.EntireColumn.AutoFit
    End With
    MsgBox ("OK")
    Application.ScreenUpdating = True

End Sub
修正一下