VBA puts -@ at the beginning of formula instead of =

[floggingmolly]

I have a VBA code that generates a filter formula. The code will update the formula if a new column is added to a table. When the code runs, it changes the = at the begging of the formula to =@. Does anyone know why this is happening or how to fix it? Below is the code:

Sub UpdateFilterFormula()
    Dim wsRawData As Worksheet
    Dim wsFiltered As Worksheet
    Dim lastColumn As Integer
    Dim formula As String
    Dim colName As String
   
    ' Set references to the "Raw Data" and "Filtered" sheets
    Set wsRawData = ThisWorkbook.Worksheets("Raw Data")
    Set wsFiltered = ThisWorkbook.Worksheets("Filtered")
   
    ' Find the last column in "Table1" in the "Raw Data" sheet
    lastColumn = wsRawData.ListObjects("Table1").ListColumns.Count
   
    ' Initialize the formula without the "=" sign
    formula = "FILTER(Table1, "
   
    ' Loop through the columns in "Table1" and add them to the formula
    For i = 1 To lastColumn
        colName = wsRawData.ListObjects("Table1").ListColumns(i).Name
        formula = formula & "ISNUMBER(SEARCH(B2, Table1[" & colName & "]))"
       
        ' Add a "+" if it's not the last column
        If i < lastColumn Then
            formula = formula & "+"
        End If
    Next i
   
    ' Add the final part of the formula
    formula = formula & ",""No Match"")"
   
    ' Set the Formula property with "=" to ensure it starts with "="
    wsFiltered.Range("B5").formula = "=" & formula
End Sub

 

[Armstrong_N14]

I guess I can use conditional formatting with the original formula so if any cell contains 0 then just change the font to white

sorry, I realised that late. Conditional formatting should do the trick.