How to use column header in macro

[Melzebu]

Hi everyone,

I'm quite new with macros in excel so I'm relying on your expertise.
Let's say I have a table with headers and I'm using the count formula in a macro and this is the code:
Sub Test()
'
' Test Macro
'
'
ActiveCell.FormulaR1C1 = "=COUNT(Sheet1!C)"
End Sub

The header of the column would be "Apples".
How can I change the code to have the count performed in the column called "Apples"? And where exactly within the initial code should I put it?

I hope my question is clear.
Thank you for your help

 

[Anthony47]

If your is a structured table then the formula would be, simply:

=COUNT(YourTableName[Apples])

Use ".Formula" (and not .FormulaR1C1) to insert it into a cell

Bye

 

[wideboydixon]

Sub Test()

Dim foundCol As Variant

foundCol = Application.Match("Apples", Range("1:1"), 0)
If Not IsError(foundCol) Then
    ActiveCell.Formula = "=COUNT(Sheet1!" & Cells(1, foundCol).EntireColumn.Address & ")"
End If

End Sub

WBD

 

[Melzebu]

Thank you for your replies.
WBD's advice worked but I have an additional question now.
I'm trying to calculate an average and I'm using a macro like this:
Sub Average()
'
' Average Macro
'
'
ActiveCell.FormulaR1C1 = _
"=IFERROR(AVERAGEIF('Report'!C[-2],""Severity 1"",'Report'!C[3]),""-"")"
End Sub

I would like to change 'Report'!C[-2] with the column header "Severity" and 'Report'!C[3] with the column header "THT".
How can I change it?

Thank you again for your help.
Cheers

 

[Melzebu]

Sub Test()

Dim foundCol As Variant

foundCol = Application.Match("Apples", Range("1:1"), 0)
If Not IsError(foundCol) Then
    ActiveCell.Formula = "=COUNT(Sheet1!" & Cells(1, foundCol).EntireColumn.Address & ")"
End If

End Sub

WBD

Hi WBD,
do you think you could help me also with the averageif formula in my last comment?

Thank you a lot