Excel VBA - Adopt formula to ActiveCell

[2Took]

Hi,

I intend to trigger from Personal Macro Workbook by a custom button in QAT.

Act upon an ActiveCell.

Active cell is going to be in a Header Row of a spreadsheet. Header Row can be in any row, not necessarily in row one. ActiveCell in any column.

Need VBA to adopt the below formula, such that:

1. "XX" in it would be replaced by the existing content of the ActiveCell
2. "A4" in it would be replaced by the Column letter of the ActiveCell and a row number below the ActiveCell
3. Letter in "A1000000" in it would be replaced by the Column letter of the ActiveCell

*Additional caveat is that Excel does not allow easily to enter a formula into a header, but that's secondary...

="XX. Tot: " & COUNTA(A4:A1000000) & " Vis: " & AGGREGATE(3,3,A4:A1000000)

 

[Joe4]

@Joe4.
Hence the "Pretty well the same."

Just clarifying that it really isn't "another way", but really the same way that was already discussed.

 

[jolivanes]

Excuse me for showing someone how to fish a (very) slightly different way so he/she will be fed for life.

A Merry Christmas and a Happy, Healthy and Prosperous New Year to you and yours.

 

[Joe4]

Excuse me for showing someone how to fish a (very) slightly different way so he/she will be fed for life.

Sorry, didn't mean to upset you.

I looked a little more closely, and just picked up on that subtle difference around when to turn on the Macro Recorder versus when the formula is recorded. I did not notice the the first time around (I have been very distracted lately with a lot going on that really limits my time of this board there days).

Yep, I have used that variation too, specifically when I already have a cell with the formula I need already entered.
Good tip that might save them some time, especially if it is a long complex formula!

 

[jolivanes]

No need to apologize Joe. I am old (and experienced I hope) enough to not be bothered by little things. I will answer as I see fit though.

A Merry Christmas, Happy, Healthy and Prosperous New Year to you and yours. I hope you will not be too stressed during these days.