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]

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]

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]

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.

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]

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

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]

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.

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]

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]

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]

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]

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.

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]

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