CODEWITHSUNDEEP
excelvba
CATSandCATSandCATS
CATSandCATSandCATS

Reputation: 312

How do I sum a variable range?

I have a macro which imports weekly data into a new column and then runs a number of operations on it. I am having great difficulty with trying to sum the past ten weeks' data. Obviously formulae do not work since every week when I insert a new column, the formulae would not move to include the new column and drop the eleventh column.

The code I wrote to take this is:

        Dim h As Range
        Dim preCol As Long
            With wsBOS.Rows(7)
            Set h = .Find("Total", LookIn:=xlValues)
                If Not h Is Nothing Then
                    preCol = h.Column - 1
                End If
            End With

        For jCombo = 1 To 175
        Dim siteCombo As String
        siteCombo = ThisWorkbook.Sheets("Results Sheet").Cells(jCombo, 3)
            If ((siteCombo = "Bone & Connective Tissue") Or (siteCombo = "Brain/CNS") Or (siteCombo = "Breast") Or (siteCombo = "GI") Or (siteCombo = "Gland/Lymphatic") Or (siteCombo = "GYN") _
                Or (siteCombo = "Head & Neck") Or (siteCombo = "Leukemia Lymphoma") Or (siteCombo = "Lung") Or (siteCombo = "Gu") Or (siteCombo = "GU") Or (siteCombo = "Male") _
                Or (siteCombo = "Metastasis Genital Organ") Or (siteCombo = "Other") Or (siteCombo = "Skin")) Then
                        ThisWorkbook.Sheets("Results Sheet").Cells(jCombo, preCol - 2).Value = Application.Sum(Range(Cells(jCombo, preCol - 11), (Cells(jCombo, preCol - 3))))
            End If
        Next jCombo

where jCombo increments rows downwards and preCol refers to the newly created column.

For whatever reason, this snippet is simply doing nothing when run. It does not throw any errors, it just leaves all 175 rows of preCol untouched. I am stumped and am turning to you for help.

If anyone has any ideas and wants to share them, I will be beyond appreciative. Thank you!!

Sample of Results Sheet

preCol refers to Column OL.

I am trying to get the value to populate in column OJ.

Upvotes: 0

Views: 68

Answers (2)

urdearboy
urdearboy

Reputation: 14580

This seems to be working for me

Option Explicit

Sub Dynamic_Duo()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

Dim LC As Long, LR As Long, SumRange As String

LC = ws.Cells(6, ws.Columns.Count).End(xlToLeft).Offset.Column
LR = ws.Range("B" & ws.Rows.Count).End(xlUp).Offset(-2).Row

ws.Cells(6, LC + 1) = "10 Week"
ws.Cells(7, LC + 1) = "Total"
    SumRange = ws.Range(ws.Cells(8, LC - 9), ws.Cells(8, LC)).Address(False, False)
    ws.Range(ws.Cells(8, LC + 1), ws.Cells(LR, LC + 1)).Formula = "=Sum(" & SumRange & ")"

MsgBox "@Scott Craner's solution is better", vbCritical

End Sub

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

Put this in Row 8 and copy down:

=SUM(INDEX(8:8,COLUMN()-1):INDEX(8:8,COLUMN()-10))

Since there are no column references it will always look at the last 10 columns in row 8. The 8:8 will change to the next row as it is dragged down.

Upvotes: 2

Related Questions