MS Excel / VBA help thread

The issue you're encountering is likely due to the way the Keywords.Address function is being used and how the formula is being constructed. The Keywords.Address function is generating an absolute reference (e.g., Sheet3!$A$1:$A$749), and when combined with the SEARCH function, it results in an array formula. However, the @ symbol is being added automatically by Excel to indicate implicit intersection, which can cause unexpected behavior.

To construct the formula correctly in VBA, you need to ensure that the Keywords.Address function is used properly and that the formula string is constructed without introducing unwanted characters. Here's a revised version of your VBA code:

Dim MyFormula As String
Dim Keywords As Range
Dim ColumnNumber As Integer

' Assuming Keywords and ColumnNumber are already defined
MyFormula = "=TEXTJOIN(" & Chr(34) & ", " & Chr(34) & ",TRUE,IF(ISNUMBER(SEARCH(" & _
            Keywords.Address(ReferenceStyle:=xlR1C1, External:=True) & "," & _
            "RC" & ColumnNumber & "))," & _
            Keywords.Address(ReferenceStyle:=xlR1C1, External:=True) & "," & _
            Chr(34) & Chr(34) & "))"

' Example of setting the formula to a cell
Range("B2").Formula = MyFormula

Explanation:
Keywords.Address: This function generates the address of the Keywords range in R1C1 reference style.
Chr(34): This is used to insert double quotes (").
Formula Construction: The formula is constructed as a string in VBA, ensuring that all parts are correctly concatenated.