CODEWITHSUNDEEP
excelvba
CATSandCATSandCATS
CATSandCATSandCATS

Reputation: 312

Referencing a new column without a fixed address in VBA

I am trying to figure out how to reference the column immediately preceding a column labeled "Total".

My macro is supposed to find the column labeled "Total", insert a row before it, and then loop through a SUMIF function for the specified cells. I got the inserting a column portion working, but I cannot figure out how to then reference that new column for writing the SUMIF to.

I am trying to get the reference where I wrote [[HERE]].

Any ideas on how to get this reference working?

Thanks!!

And for transparency, I got the inserting a column code from another user's question asked on here. 

For s = LBound(sheetArray) To UBound(sheetArray)
        With sheetArray(s)
            With .Rows(7).SpecialCells(XlCellType.xlCellTypeConstants, xlTextValues)
                Set f = .Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole)
                If Not f Is Nothing Then
                    firstAddress = f.Offset(, 1).Address 
                    Do
                        f.EntireColumn.Insert
                        Set f = .FindNext(f)
                    Loop While f.Address <> firstAddress
                End If
            End With
        End With
    Next s

'The above works.

    For s = 1 To UBound(sheetArray)
        With sheetArray(s)
            For i = 8 To 21
                Set criteriaDept = sheetArray(s).Cells("B7")
                Set criteriaSite = sheetArray(s).Cells(i, 3)
                Set criteriaTherapy = sheetArray(s).Cells("C6")
                    sheetArray.Cells(i, [[HERE]]) = SumIf(sumIfRange, cSiteRange, criteriaSite,       
                        cDeptRange, criteriaDept, cTherapyRange, criteriaTherapy)
            Next i
        End With
    Next s

Upvotes: 0

Views: 114

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

First, get the column number that is the one immediately preceding "Total" (by "preceding", I assume you mean to the left):

Dim preCol as Long
preCol = Sheets("Sheet1").Rows(1).Find(what:="Total").Offset(0, -1).Column
'Now, you can use that variable:
' ...whatever
sheetArray.Cells(i, preCol) = ...

Note that you'll likely need to change/update Sheets("Sheet1") to whatever sheet has that column header.

Upvotes: 1

Related Questions