1

I'm trying to sum the contents of columns in Sheet1 that are 6 cells apart (i.e. the first is G19:G28, the second is M19:M28) using a for loop to define the column value. So, I want the sum of the G column, the sum of the M column, and so on, but only if the column value is greater than 0. The sums are then assigned to consecutive cells in a sheet called 'Calculator.' This is what I have so far, but it is not working. Any help is appreciated!

    For m = 1 To 20
    totdamage = Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range(Cells(19, (6 * 
    m) + 1), Cells(28, (6 * m) + 1)), ">0", Worksheets("Sheet1").Range(Cells(19, (6 * m) + 
    1).Value, Cells(28, (6 * m) + 1).Value)):
    Worksheets("Calculator").Cells(m + 6, 31).Value = totdamage
    Next m
Emily
  • 15
  • 3
  • 1
    You need to qualify the `Cells` calls with the `Worksheet` as well... see https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet – BigBen Jun 14 '21 at 16:05
  • I've received mixed feedback from other editors etc. but will state for record I (personally) appreciate / favour Qs with a screenshot or sample data to help visualise the set up. / provide some better 'description' / intuition re the problem at hand. Think the takeaway is the more 'clear' and easy to understand the Q, the better! – JB-007 Jun 14 '21 at 17:29
  • Sorry, James, some of the data is under an NDA so I couldn't take screenshots, but I appreciate the suggestions for making my question better! – Emily Jun 14 '21 at 18:27
  • Thanks BigBen, I appreciate it! – Emily Jun 14 '21 at 18:28

1 Answers1

0

SumIf in VBA

A Quick fix

Option Explicit

Sub SumToCalculator()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    Dim dws As Worksheet: Set dws = wb.Worksheets("Calculator")
    
    Dim srg As Range
    Dim m As Long
    Dim TotDamage As Double
    
    For m = 1 To 20
        Set srg = sws.Rows("19:28").Columns("G").Offset(, (m - 1) * 6)
        TotDamage = Application.SumIf(srg, ">0", srg)
        dws.Cells(m + 6, "AE").Value = TotDamage ' first in 'AE7'
    Next m

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28