Using variable as column reference to autosum said column VBA

Question

I'm really new to VBA and have been working section by section on a number of pieces of code to format a worksheet (I've been doing it piece by piece so that I understand how each works, and using a final macro to Call all the macros into one long process).

Issue is sometimes the worksheets I work with are not exported with columns in the same order from month to month (out of my control), thus to autosum a particular column I have to Find the column header, then autosum that column, but this makes the column letter(or number) completely variable. I know how to work with rows as variables, but I'm stuck on column. I've been scouring forums to try and find a concise explanation, but to no avail, yet.

This code DOES work for column Y specifically, but I'm trying to figure out how to get it to use a variable for the column. For example, I'm using a separate Macro called "FindInvoiceColumn" to select the 1st cell in the column that contains the string "invoice_amount", then I'd like to use something like I wrote below to set "ColumnAddress" as the column value of that cell. As far as I know .Column returns the column number, which is fine, but I'm assuming I'd have to use with Cells() instead of Range(), I just don't know how to get here.

(Part of the code also shows Adding the word "Total" to the left of the cell containing the autosum value, and making both bold).

Here's what I have so far:

Dim Rng As Range
   Dim c As Range

   Set Rng = Range("Y" & rows.Count).End(xlUp).Offset(1, 0)
   Set c = Range("Y1").End(xlDown).Offset(1, 0)
   c.Formula = "=SUM(" & Rng.Address(False, False) & ")"

   'Selects next empty row of column X to add "Total" label for sum of column Y'
    Range("X" & Cells.rows.Count).End(xlUp).Offset(1, 0).Select
   ActiveCell.FormulaR1C1 = "Total"

   'Bolds Total and the Sum of invoices'
   Range("X" & Cells.rows.Count).End(xlUp).Select
   Selection.Font.Bold = True
   Range("Y" & Cells.rows.Count).End(xlUp).Select
   Selection.Font.Bold = True```




'The below is what I'd like to use to find the dynamic value of the column.'

'Finds cell in row 1 that contains column header "invoice_amount" and selects it'
 Call FindInvoiceColumn

'Dim ColumnAddress As Integer
 ColumnAddress = ActiveCell.Column

Answer 1

You can use .Address to get a column reference, such that:

Sub test()
    Dim varCol As String
    varCol = Columns(ActiveCell.Column).Address
    Debug.Print varCol 'OUTPUTS $A:$A when I had cells(1,1) selected
End Sub

In the above example, I chose a single cell to A) find it's column reference, via .Column, and B) found the .address of said column.

---

You could also perform the sum on a defined range using .cells() notation, rather than .range() notation.

Sub test2()
    Dim rng As Range
    Set rng = Range(Cells(1, 1), Cells(2, 1))
    Cells(3, 1).Formula = "=sum(" & rng.Address & ")"
End Sub

The above code ouputs:

---

Specific to using the .cells() notation, you can make your column reference a variable, e.g.:

dim r as long, c as long
r = 1
c = 4
debug.print cells(r,c).address `should output $D$1 (untested)

You can choose r or c to fit your needs.

---

And as always... avoid select/activate where possible!!!

---

---

Edit

Adding use of last row via code since comments are terrible:

dim col as long
col = 25 'Y
With sheets("name")
    dim lastRow as long
    lastRow = .cells(.rows.count,col).end(xlup).row
    Dim rng As Range
    Set rng = .Range(.Cells(1, 1), .Cells(lastRow, col))    
end with

This is exactly why I mentioned the specifics abotu the notation after that section (use of r and c as variables).

Answer 2

I've used this code to set a column number if your header is in a variable position

Dim F As Object
ColumnAddress = 0
With ActiveSheet.Rows(1)
        Set F = .Find(What:="invoice_amount", LookAt:=xlWhole)
        If F Is Nothing Then
            MsgBox "This is not a proper file"
            ' other code
        Else 
            ColumnAddress = F.Column
        End If
End With

You would then use Cells() in place of range to do further work with the result of ColumnAddress. Also, ColumnAddress should dim as Long, to be accurate.