sheet Formula in vba

[Dak2]

I am in need of some help to figure out an issue with my Excel Application. I have a spreadsheet formula that I need to insert into a cell every time this specific sheet is activated, or a textbox object on a user form is clicked. I was successful in doing what I intended to do and the code is very simple, is below :
However, on one of my computers, this code works fine and the formula is inserted with no issues. But on another computer, the same code fails at this line ActiveSheet.Range("C2") = myFPA due to 'Run-time error '1004': Application-defined or object-defined error', I changed the ActiveSheet.Range to - Set x = workSheets("Sheet1") and then doing the assignment accordingly, which is: x.Range("C2") = myFPA. But failed again on the same computer but not the other. I am suspecting that something in my setting is causing this- but there is nothing obvious to me except the version and build.
The one that works has the version as 2005-MS Excel 365 MSO(12827.20268)32-bit.
And the one that doesn't work has the version as 1908 (Build 11929.20562),
please let me know if you saw a problem like this before. Please note, I have also tried
ActiveSheet.Range("C2").Formula = myFPA
ActiveSheet.Range("C2").FormulaArray = myFPA
I got the idea of this routine from a post here on mrexcel, but this issue was not mentioned on the post. Thank you for you assistance

Sub insertSheetFormula()
Dim myFPA As String
Dim myFPB As String


myFPA = "=TEXTJOIN("""",True,""X()"")"

myFPB = "IF(A:A = G4, B:B,"""")"

myFPA = Replace(myFPA, """X()""", myFPB)

ActiveSheet.Range("C2") = myFPA

End Sub

'*******************************************************************
'
'This is the Sheet Formula---> =TextJOIN("",TRUE,If(A:A=G4,B:B,""))
'*******************************************************************
'*******************************************************************
'Here the sheet formula broken into two sections
'*******************************************************************
'myFPA = TextJOIN("",True, ""X()"")"
'myFPB = If(A:A = G4, B:B, "")
'*******************************************************************

 

[Yongle]

Welcome to the forum
Please note for the future that clicking on <vba/> icon and pasting code between the code tags makes code appear more like it does in VBA editor (see below) which is easier to read
(use <rich/> if you want to highlight any part of the code using font formatting)

This works for me, but my build is very recent

Sub insertSheetFormula()
    Dim myFPA As String
    Dim myFPB As String
    myFPA = "=TEXTJOIN("""",True,""X()"")"
    myFPB = "IF(A:A = G4, B:B,"""")"
    myFPA = Replace(myFPA, """X()""", myFPB)
    ActiveSheet.Range("C2").FormulaArray = myFPA
End Sub

What happens when you enter the array formula manually into cell C2 (on the "broken" computer) ? (confirm with CTRL_SHIFT_ENTER)
=TEXTJOIN("",0,IF(A:A = G4, B:B,""))

 

[Dak2]

Thank you Yongle for your help.
It does work when I enter the formula array manually into C2 on the broken computer.
Could it be that the the Symantec firewall is causing this?
The broken computer is on company computer.

 

[Yongle]

If that is the case then any formula would presumably cause the same problem

Try something very simple
eg

Range("C4").Formula = "=(A2)"
Range("C5").FormulaArray = "=(A2)"

 

[Dak2]

Yongle,
Your experiment works. The formula was inserted on both cells.

 

[Yongle]

Where is procedure InsertSheetFormula - in a standard module ? in a sheet module ? in userform module ? in a different workbook ?

How are you calling InsertSheetFormula ?
- from Worksheet_Activate ?
- from Workbook_SheetActivate ?
- from userform Textbox_Click ?

Why are you inserting the formula in the cell EVERY time ? - why is it not there already ?

Are calculations set to manual in your workbook ?

 

[Dak2]

The procedure is in a standard module, for the time being.
I am using it in a worksheet_Activate, will be moved to a user-form Textbox_Click in the future, in an application with User Form and other functions. But because i had all these issues with it I created a separate workbook with just one module to run experiments and trouble shoot each scenario. I stripped it down to just a few components- A workbook with a standard module, that's it.

Why I am inserting the formula in the cell every time?
Good question, in the original application the value at C2 which is the result of the textjion formula, is fed to a variable and used somewhere else in the application.

Why is it not there already?
Good question, but every time the C2 value is fed to a variable the formula disappears and that doesn't work if my intend is full automation.

Are calculations set to manual in your workbook ?
Calculations set to Automatic (Default) never been changed.

 

[Yongle]

Two workarounds for you to test
Option 1 would be simplest method - let's hope it works for you

1. Use a different formula in C2
Enter the formula manually. It is NOT an array formula. Does it return the correct value?
=REPT(0,COUNTIF(A:A,G4))
If so, use this VBA

ActiveSheet.Range("C2").Formula = "=REPT(0,COUNTIF(A:A,G4))"

If option 1 above does not work for you ...
2. In cell C1 enter this array formula manually (confirm with CTRL_SHIFT_ENTER)
=TEXTJOIN("",TRUE,IF($A:$A = G3, $B:$B,""))
and use this VBA

Sub CopyFormula()
    Range("C1").Copy Range("C2")
End Sub

 

[Dak2]

My, oh my!
Eureka!

 

[Dak2]

Thank you Yongle,
This one works!
Range("C1").Copy Range("C2")
But how does G3 becomes G4?
Just wondering, for future reference.

Thanks again