# Excel VBA - Adopt formula to ActiveCell



## 2Took (Dec 16, 2022)

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:

"XX" in it would be replaced by the existing content of the ActiveCell
"A4" in it would be replaced by the Column letter of the ActiveCell and a row number below the ActiveCell
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 (Dec 19, 2022)

2Took said:


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



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 (Dec 19, 2022)

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 (Dec 19, 2022)

Joe4 said:


> 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:
> ...


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 (Dec 19, 2022)

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 (Dec 19, 2022)

Joe4 said:


> 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?
> ...


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 (Dec 19, 2022)

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 (Dec 19, 2022)

Joe4 said:


> 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.
> ...



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 (Dec 19, 2022)

2Took said:


> 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:
> 
> ...


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 (Dec 19, 2022)

Joe4 said:


> 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)"
```


----------



## 2Took (Dec 16, 2022)

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:

"XX" in it would be replaced by the existing content of the ActiveCell
"A4" in it would be replaced by the Column letter of the ActiveCell and a row number below the ActiveCell
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 (Dec 19, 2022)

OK, what can get really confusing is the double-quote is used as a text qualifier in VBA.  When you want a literal double-quote, things can get a bit messy, as you need to include multiple double-quotes in a row.  If I am being honest, I too find it sometimes confusing.

Instead what I usually do is when I need a literal double-quote to be printed in VBA is to use *Chr(34)* instead (which is the ASCII code for double-quotes).
If you do that in all the places where you need literal double-quotes, you will never have to have more than one double-quote in a row in your VBA code.

So for example, if I wanted "Dog" to appear in cell A1 (with the double-quotes around it), it can be done like this:

```
Range("A1").Formula = """Dog"""
```
but I find it confusing on whether I need two or three consecutive double-quotes.

So I prefer to do it like this instead:

```
Range("A1").Formula = Chr(34) & "Dog" & Chr(34)
```

To me, it just make sense.
Then in my VBA code, I know that all double-quotes are simply text qualifiers, and all instances of *Chr(34)* are literal double-quotes.


----------



## Joe4 (Dec 19, 2022)

If you work it all out according to my tips, I think this will do what you need:

```
myfmula = "=" & Chr(34) & headerValue & ". Tot: " & Chr(34) & " & COUNTA(" & columnLetter & ActiveCell.Row + 1 & ":" & columnLetter & "1000000) & " & _
        Chr(34) & " Vis: " & Chr(34) & " & AGGREGATE(3,3," & columnLetter & ActiveCell.Row + 1 & ":" & columnLetter & "1000000)"
```


----------



## 2Took (Dec 19, 2022)

Joe4 said:


> OK, what can get really confusing is the double-quote is used as a text qualifier in VBA.  When you want a literal double-quote, things can get a bit messy, as you need to include multiple double-quotes in a row.  If I am being honest, I too find it sometimes confusing.
> 
> Instead what I usually do is when I need a literal double-quote to be printed in VBA is to use *Chr(34)* instead (which is the ASCII code for double-quotes).
> If you do that in all the places where you need literal double-quotes, you will never have to have more than one double-quote in a row in your VBA code.
> ...


Interesting, but whether it's *Chr(34) *or "", it does not appear to be what the issue is -- in this case, rather the difference in structure not applicable between the two formulas - don't you think?


----------



## 2Took (Dec 19, 2022)

Joe4 said:


> If you work it all out according to my tips, I think this will do what you need:
> 
> ```
> myfmula = "=" & Chr(34) & headerValue & ". Tot: " & Chr(34) & " & COUNTA(" & columnLetter & ActiveCell.Row + 1 & ":" & columnLetter & "1000000) & " & _
> ...


Thank you for figuring this bit out -- it worked out nicely!
What do you think of the "I got the code by feeding my original post to ChatGPT" by OpenAi aspect of it?


----------



## Joe4 (Dec 19, 2022)

Basically, all I did was use the MsgBox, and started building the formula bit-by-bit, adjusting it to look the way it needs to.  It took a few minutes and some testing, but essentially got it to where it needs to be.



2Took said:


> What do you think of the "I got the code by feeding my original post to ChatGPT" by OpenAi aspect of it?


I am not familiar with that product, so I really cannot comment on it.


----------



## 2Took (Dec 19, 2022)

Joe4 said:


> Basically, all I did was use the MsgBox, and started building the formula bit-by-bit, adjusting it to look the way it needs to.  It took a few minutes and some testing, but essentially got it to where it needs to be.


That's a good way to think of it - thank you.  (essentially vs eventually)

ChatGPT is a new AI chatbot that can answer questions and write essays, among many other things. It's a free web platform - interacting with Ai in a form of a Chat.
Stands for Chat Generative Pre-trained Transformer, is a chatbot developed by OpenAI. ChatGPT is built on top of OpenAI's GPT-3.5 family of large language models, and is fine-tuned with both supervised and reinforcement learning techniques.

- You'll definitely hear about it!


----------



## Joe4 (Dec 19, 2022)

2Took said:


> ChatGPT is a new AI chatbot that can answer questions and write essays, among many other things. It's a free web platform - interacting with Ai in a form of a Chat.
> Stands for Chat Generative Pre-trained Transformer, is a chatbot developed by OpenAI. ChatGPT is built on top of OpenAI's GPT-3.5 family of large language models, and is fine-tuned with both supervised and reinforcement learning techniques.
> 
> - You'll definitely hear about it!


Interesting.

Well, if that is what help you come up with the code but had issues, hopefully they will make some improvements along the way.


----------



## jolivanes (Dec 19, 2022)

Joe4 showed you a little trick. Here is another way. Pretty well the same.
Enter the formula in a cell as you would for a regular worksheet formula.
Select the cell
Tools, Macro, Record
F2, Enter
Now go and check what that macro turns into when you go to Developer - Macros - select the macro you just saved and click on Edit.
It'll show you how it needs to look like in a macro.


----------



## Joe4 (Dec 19, 2022)

jolivanes said:


> Joe4 showed you a little trick. Here is another way. Pretty well the same.
> Enter the formula in a cell as you would for a regular worksheet formula.
> Select the cell
> Tools, Macro, Record
> ...


I think you might have missed post 7 and the ensuing conversation! 








						Excel VBA - Adopt formula to ActiveCell
					

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...




					www.mrexcel.com


----------



## jolivanes (Dec 20, 2022)

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


----------



## 2Took (Dec 16, 2022)

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:

"XX" in it would be replaced by the existing content of the ActiveCell
"A4" in it would be replaced by the Column letter of the ActiveCell and a row number below the ActiveCell
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 (Dec 20, 2022)

jolivanes said:


> @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 (Dec 20, 2022)

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 (Dec 20, 2022)

jolivanes said:


> 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 (Dec 20, 2022)

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.


----------

