[已解决][求助]用循环、数组求项目合计数

zlj

[求助]用循环、数组求项目合计数

用代码如上图，谢谢！有最佳。

最佳答案

月排行榜 / 总排行榜

搁浅2008

2009-11-8 10:11

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$10" Then
        Dim i As Long
        Dim j As Long
        Dim rng As Range
        Dim 数量 As Double
        Dim 金额 As Double
        For Each rng In Range("A2:E7")
            If rng = Range("B10") Then
                If Cells(1, rng.Offset(, 1).Column) = "数量" Then
                    数量 = 数量 + rng.Offset(, 1)
                ElseIf Cells(1, rng.Offset(, 1).Column) = "金额" Then
                    金额 = 金额 + rng.Offset(, 1)
                End If
                If Cells(1, rng.Offset(, 2).Column) = "数量" Then
                    数量 = 数量 + rng.Offset(, 2)
                ElseIf Cells(1, rng.Offset(, 2).Column) = "金额" Then
                    金额 = 金额 + rng.Offset(, 2)
                End If
            End If
        Next rng
        Range("C10") = 数量
        Range("D10") = 金额
    End If
End Sub

跳转到最佳答案楼层

windvchina

q56i4les.rar (8.53 KB, 下载次数: 1)

2009-11-7 23:21 上传

点击文件名下载附件
[求助]用循环、数组求项目合计数

试下吧。。你得传个附件来瞅瞅的咧。

zlj

不平均的列。

zlj

NIIsC02y.rar (7.82 KB, 下载次数: 1)

2009-11-7 23:34 上传

点击文件名下载附件
[求助]用循环、数组求项目合计数

zlj

数量和金额出现的列是不规则的。

zsd5237

学习[em02][em02]

FookYou

  dGj9eKhP.rar (9.02 KB, 下载次数: 1)

2009-11-8 10:38 上传

点击文件名下载附件

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Col%, Sl!, Je!
  Dim Rng As Range
  Dim C As Range
    If Target.Address = "$B$10" Then
      Col = Sheet1.UsedRange.Columns.Count
      For i = 1 To Col
        If Cells(1, i) = "项目" Then
          Set Rng = Range(Cells(2, i), Cells(7, i))
          For Each C In Rng
            If C = [B10] Then
              If Cells(1, C.Column + 1) = "数量" Then
                Sl = Sl + C.Offset(, 1)
              End If
              If Cells(1, C.Column + 1) = "金额" Then
                Je = Je + C.Offset(, 1)
              End If
              If Cells(1, C.Column + 2) = "金额" Then
                Je = Je + C.Offset(, 2)
              ElseIf Cells(1, C.Column + 2) = "数量" Then
                Sl = Sl + C.Offset(, 2)
              End If
            End If
          Next C
        End If
      Next i
      [C10].Value = Sl
      [D10].Value = Je
    End If
End Sub

[此贴子已经被作者于2009-11-8 10:37:38编辑过]

zlj

A的数量合计数为25.金额合计数应为24

[此贴子已经被作者于2009-11-8 9:36:36编辑过]

搁浅2008

kmh6Wsnv.rar (8.9 KB, 下载次数: 6)

2009-11-8 10:10 上传

点击文件名下载附件
[求助]用循环、数组求项目合计数

搁浅2008

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$10" Then
        Dim i As Long
        Dim j As Long
        Dim rng As Range
        Dim 数量 As Double
        Dim 金额 As Double
        For Each rng In Range("A2:E7")
            If rng = Range("B10") Then
                If Cells(1, rng.Offset(, 1).Column) = "数量" Then
                    数量 = 数量 + rng.Offset(, 1)
                ElseIf Cells(1, rng.Offset(, 1).Column) = "金额" Then
                    金额 = 金额 + rng.Offset(, 1)
                End If
                If Cells(1, rng.Offset(, 2).Column) = "数量" Then
                    数量 = 数量 + rng.Offset(, 2)
                ElseIf Cells(1, rng.Offset(, 2).Column) = "金额" Then
                    金额 = 金额 + rng.Offset(, 2)
                End If
            End If
        Next rng
        Range("C10") = 数量
        Range("D10") = 金额
    End If
End Sub