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]

Glad it worked - very strange problem!

Range("C1").Copy Range("C2")
But how does G3 becomes G4?
Just wondering, for future reference.

It's a straightforward copy and paste like in EXCEL, where formula is automatically re-referenced
If the formula is only intended for cell C2 (ie it does not require to be copied down the column) then use this as the base formula
=TEXTJOIN("",TRUE,IF($A:$A = $G$4, $B:$B,""))

Above formula can be placed in any cell from which it can be copyied and pasted to cell C2