vba - Excel VBA : Summing values in a row only when column heading meets a condition

Question

我有一个电子表格，其中销售数据与 N:AD 列中的其他数据散布在一起。销售的列标题始终以“销售”一词结尾(例如，“2015-06-销售”。我希望仅当标题以“销售”一词结尾时，才对每行的数据进行求和。我需要使用vba，因为列名称会随着时间的推移而变化。

示例数据(我希望 F 列对以 sales 结尾的列进行求和，或者在下面的情况下对 N 和 P 进行求和):

       Column F     Column N        Column O     Column P
       Total Sales  2015-05-Sales   2015-05-Qty  2015-01-Sales
Item1    20            5               30            15   
Item2    15            5               1             10  
Item3    10            1               2             9 

这是我到目前为止的代码:

Sub test()
Dim lcolumn As Long
Dim lrow As Long
Dim SSold

For lrow = 2 To 100 Step 1
    For lcolumn = 14 To 30 Step 1
        If Right(ws.Range(1 & lcolumn), 5).Value = "Sales" Then
            SSold = 0
            SSold = SSold + Range(lrow & lcolumn)
        End If
    Next lcolumn
    Range(lrow & 6).Value = SSold
Next lrow

End Sub

它会创建运行时错误“1004”:对象“_Global”的方法“Range”在以“如果正确(范围...”开头的行上失败)

感谢任何帮助!

Answer 1

试试这个:

Sub SumSales()
    Dim total As Double

    For iRow = 2 To 100      'no need of Step 1, test as many rows as you wish
        total = 0            'resets the total for each row
        For iCol = 14 To 30  'tests as many columns as you wish
            If Right(Cells(1, iCol).Value, 5) = "Sales" Then
                total = total + Cells(iRow, iCol).Value
            End If
        Next iCol            'this loop will go on for each column in iRow row
        Cells(iRow, 6).Value = total  'store the total before going to the next row
    Next iRow
End Sub