Use the address of a header cell to reference the range below it within a worksheetfunction.

[Qazzart]

Hi Everyone,

So Im stumped, and I have found no soulutions online as of yet.
Im using Excel 2010.

I recive a report that never has the headers in the same spot twice, so I cant reference them by cell.
I figured out how to reference the first unused column in the worksheet, then flood fill it with my formulas... as seen below:

sub test()

'....some code here.....

j = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column + 1
i = ActiveSheet.UsedRange.Rows.Count

Set rngBackordered = ActiveSheet.UsedRange.Find("Backordered", , xlValues, xlWhole)
Backordered = rngBackordered.Offset(1, 0).Address(False, False)

Set rngLLTMat = ActiveSheet.UsedRange.Find("LLT Mat", , xlValues, xlWhole)
LLTMat = rngLLTMat.Offset(1, 0).Address(False, False)

Set rngInPurchase = ActiveSheet.UsedRange.Find("In Purchase", , xlValues, xlWhole)
InPurchase = rngInPurchase.Offset(1, 0).Address(False, False)

Cells(1, j).Value = "LLT Unawarded"
    With Range(Cells(2, j), Cells(i, j))
        .Formula = "=IF(OR(AND(" & LLTMat & "=""Yes""," & Backordered & "=""Yes""),AND(" & LLTMat & "=""Yes""," & InPurchase & "=""Yes"")),""Yes"",""No"")"
        .Value = .Value
    End With

'....some code here.....

end sub

what I want to do, is (for instance with LLTMat) do a Countif and assign that to a named cell.

[TotalLLTMat] = worksheetfuntion.CountIf(Range(), "Yes")

My question is, How do I reference the entire column when I only have the header address.

as always, Thank you all for your time and your help.

~Qazzart

 

[Jerry Sullivan]

Hi Qazzart,

If you already have the range object for the found Header Cell, then you don't need to convert it to an address string to be used in a WorksheetFunction.

Instead use the object like:

To reference the entire column...

[TotalLLTMat] = WorksheetFunction.CountIf(rngLLTMat.EntireColumn, "Yes")

To reference all the cells in the column below the header...

With ActiveSheet
   Set rngLLTMat = .UsedRange.Find("LLT Mat", , xlValues, xlWhole)
   Set rngBelowLLTMat = Range(rngLLTMat(2), .Cells(.Rows.Count, rngLLTMat.Column))
End With

[TotalLLTMat] = WorksheetFunction.CountIf(rngBelowLLTMat, "Yes")

If all you had was the address string of a cell on the ActiveSheet but no range object, you could do something similar.

LLTMat = "K3"
[TotalLLTMat] = WorksheetFunction.CountIf(Range(LLTMat).EntireColumn, "Yes")

 

[Qazzart]

Jerry,

Thank you so much for your help, it worked perfectly, also thank you for explaining the command so well.

~Qazzart