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

 

[floggingmolly]

Are you definitely using Formula2 and not Formula?

wsFiltered.Range("B5").Formula2 = "=" & formula

I got it to work. Thank you so much. One other question. If there are blanks in Table1, when the results spill over it enters a 0. Do you know if it's possible to have it return a blank rather than a zero?

 

[MARK858]

Can you please post a sample of Table1 using XL2BB so we can see where this is occurring

 

[floggingmolly]

Can you please post a sample of Table1 using XL2BB so we can see where this is occurring

I don't know how to use XL2BB. I've tried several times before and can't figure it out. Basically Table1 has the data. The VBA code creates the formula on a separate sheet and the Table data spills into a range on the other sheet. I have a text box where you can type in text to filter the data. If Table1 has a blank, then when the code runs and the table data spills into the other sheet, the blanks are showing as a zero. Hope that makes sense. Not sure if there's a way to use ISBLANK or something in the formula to show a blank rather than zero

 

[MARK858]

I don't know how to use XL2BB

Then upload a sanitized version of the workbook to a free file hosting site like www.box.com or www.dropbox.com, mark the file for sharing and post the link it produces in the thread.

 

[floggingmolly]

Can you please post a sample of Table1 using XL2BB so we can see where this is occurring

Then upload a sanitized version of the workbook to a free file hosting site like www.box.com or www.dropbox.com, mark the file for sharing and post the link it produces in the thread.

Here is the link it gave me Excel Search Bar V2.xlsm | Powered by Box

 

[Armstrong_N14]

maybe this:

=SUBSTITUTE(FILTER(Table1, ISNUMBER(SEARCH(B3, Table1[Entity Name]))+ISNUMBER(SEARCH(B3, Table1[Entity ID]))+ISNUMBER(SEARCH(B3, Table1[Region]))+ISNUMBER(SEARCH(B3, Table1[District]))+ISNUMBER(SEARCH(B3, Table1[Status]))+ISNUMBER(SEARCH(B3, Table1[New column]))+ISNUMBER( SEARCH(B3,#REF!))+ISNUMBER( SEARCH(B3,#REF!)),"No Match"),0,"")

 

[floggingmolly]

maybe this:

=SUBSTITUTE(FILTER(Table1, ISNUMBER(SEARCH(B3, Table1[Entity Name]))+ISNUMBER(SEARCH(B3, Table1[Entity ID]))+ISNUMBER(SEARCH(B3, Table1[Region]))+ISNUMBER(SEARCH(B3, Table1[District]))+ISNUMBER(SEARCH(B3, Table1[Status]))+ISNUMBER(SEARCH(B3, Table1[New column]))+ISNUMBER( SEARCH(B3,#REF!))+ISNUMBER( SEARCH(B3,#REF!)),"No Match"),0,"")

View attachment 101444

That works perfect. Thank you so much. I tried all kind of things and nothing worked. You are a lifesaver. Really appreciate your help.

 

[floggingmolly]

That works perfect. Thank you so much. I tried all kind of things and nothing worked. You are a lifesaver. Really appreciate your help.

Well, this worked as far as the zeros. I just realized now my filter doesn't work. haha. If I type in the text box it no longer filters the data in the range.

 

[floggingmolly]

Well, this worked as far as the zeros. I just realized now my filter doesn't work. haha. If I type in the text box it no longer filters the data in the range.

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

 

[MARK858]

I just realized now my filter doesn't work. haha. If I type in the text box it no longer filters the data in the range.

The formula needs changing to B2

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").Formula2 = "=" & "SUBSTITUTE(" & formula & ",0,"""")"
End Sub