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)

 

[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)

So far I got the code below, but get following error on line:

activeCell.formula = formula

Sub ReplaceFormula()
    'Declare variables
    Dim aCell As Range
    Dim columnLetter As String
    Dim headerValue As String
    Dim formula As String
  
    'Get the active cell and its column letter
    Set aCell = activeCell
    columnLetter = Split(Cells(1, activeCell.Column).Address, "$")(1)
  
    'Get the value of the active cell (i.e., the header value)
    headerValue = activeCell.Value
  
    'Construct the formula using the active cell's value and column letter
    formula = "= """ & headerValue & """. Tot: " & "COUNTA(" & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000)" & " Vis: " & "AGGREGATE(3,3," & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000)"
  
    'Insert the formula into the active cell
    activeCell.formula = formula
End Sub

 

[Joe4]

NEVER, NEVER use reserved words, like "formula", as names of variables (or names of procedures)!
It can cause errors and unexpected results, as your code so clearly demonstrates.

Just look at this line here:

activeCell.formula = formula

You want the first "formula" to be the "formula" property, but the second one to be your "formula" variable.
How can VBA figure out which one you mean in each case? The ambiguity and uncertainty can lead to unexpected results amd errors!

Therefore, you should NEVER use reserved words (words of existing properties, methods, functions, events, etc) as names of your variables (or procedures or user-defined functions).
I often preface my variables with the "my" prefix to prevent this from ever happening, i.e. "myformula".

 

[2Took]

NEVER, NEVER use reserved words, like "formula", as names of variables (or names of procedures)!
It can cause errors and unexpected results, as your code so clearly demonstrates.

Just look at this line here:

activeCell.formula = formula

You want the first "formula" to be the "formula" property, but the second one to be your "formula" variable.
How can VBA figure out which one you mean in each case? The ambiguity and uncertainty can lead to unexpected results amd errors!

Therefore, you should NEVER use reserved words (words of existing properties, methods, functions, events, etc) as names of your variables (or procedures or user-defined functions).
I often preface my variables with the "my" prefix to prevent this from ever happening, i.e. "myformula".

That makes sense. I got the code by feeding my original post to ChatGPT, so blame Ai for that
I made the correction per your note, but getting the same error on the same line... And even when I changed that variable to "myfmula", same result...

Sub ReplaceFormula()
    'Declare variables
    Dim aCell As Range
    Dim columnLetter As String
    Dim headerValue As String
    Dim myfmula As String
  
    'Get the active cell and its column letter
    Set aCell = activeCell
    columnLetter = Split(Cells(1, activeCell.Column).Address, "$")(1)
  
    'Get the value of the active cell (i.e., the header value)
    headerValue = activeCell.Value
  
    'Construct the formula using the active cell's value and column letter
    myfmula = "= """ & headerValue & """. Tot: " & "COUNTA(" & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000)" & " Vis: " & "AGGREGATE(3,3," & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000)"
  
    'Insert the formula into the active cell
    activeCell.formula = myfmula
End Sub

 

[Joe4]

It probably means there is a problem with your formula.
You can insert a message box to return what the value of "myfmula" is to see if it looks like a valid formula.

So, if you add this line in your code, what exactly does the message box return?

    'Construct the formula using the active cell's value and column letter
    myfmula = "= """ & headerValue & """. Tot: " & "COUNTA(" & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000)" & " Vis: " & "AGGREGATE(3,3," & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000)"
    MsgBox myfmula

Many times when you see what you built, the issue becomes evident.

 

[2Took]

It probably means there is a problem with your formula.
You can insert a message box to return what the value of "myfmula" is to see if it looks like a valid formula.

So, if you add this line in your code, what exactly does the message box return?

    'Construct the formula using the active cell's value and column letter
    myfmula = "= """ & headerValue & """. Tot: " & "COUNTA(" & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000)" & " Vis: " & "AGGREGATE(3,3," & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000)"
    MsgBox myfmula

Many times when you see what you built, the issue becomes evident.

It gives the following:

Whereas expected is:

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

Quotations are either in the wrong place or missing, and missing ampersands...

 

[Joe4]

Here is a little trick.

Turn on your Macro Recorder and record yourself entering your desired formula in a cell in your workbook.
Then stop the Macro Recorder and view the code you just recorded.
This will show you what the structure of your formula needs to look like in VBA (especially when it comes to double-quotes).
So compare that to the code you have now, and make the necessary adjustments.

 

[2Took]

Here is a little trick.

Turn on your Macro Recorder and record yourself entering your desired formula in a cell in your workbook.
Then stop the Macro Recorder and view the code you just recorded.
This will show you what the structure of your formula needs to look like in VBA (especially when it comes to double-quotes).
So compare that to the code you have now, and make the necessary adjustments.

Right, I tried that earlier -- but it records in R1C1... That does not translate well when trying to apply that structure for the formula I am working with in this case.

Here's what the recording looks like:

    ActiveCell.FormulaR1C1 = _
        "=""XYZ. Tot: "" & COUNTA(R[1]C:R[999997]C) & "" Vis: "" & AGGREGATE(3,3,R[1]C:R[999997]C)"

 

[Joe4]

Right, I tried that earlier -- but it records in R1C1... That does not translate well when trying to apply that structure for the formula I am working with in this case.

Here's what the recording looks like:

    ActiveCell.FormulaR1C1 = _
        "=""XYZ. Tot: "" & COUNTA(R[1]C:R[999997]C) & "" Vis: "" & AGGREGATE(3,3,R[1]C:R[999997]C)"

But look closely at their use of double-quotes, especially at the beginning of the formula.
They have them doubled-up where you have them tripled up.

 

[2Took]

But look closely at their use of double-quotes, especially at the beginning of the formula.
They have them doubled-up where you have them tripled up.

That's what I am saying, doesn't translate well in this case:

with VBA line:

myfmula = "="" & headerValue &  Tot: "" & COUNTA(" & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000) & "" Vis: "" & AGGREGATE(3,3," & columnLetter & activeCell.Row + 1 & ":" & columnLetter & "1000000)"