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

F

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
167
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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
 
Last edited by a moderator:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
floggingmolly said:
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:

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
[\code]
Click to expand...
One other question I forgot, when the formula returns the data, if there are blanks in Table1, it is returning a 0 in the Filtered sheet. Is there a way to return blanks if the cell in Table1 is blank?
 
Upvote 0
I think thats what happens when you use macro to paste formula in a cell. It happened to me just that my code to put a formula in a cell using macro was different.
the @ is automatically added if i use this:

Sheets("Sheet1").Range("A1").value = 'your formula'

but the @ no longer shows if I use:
Sheets("Sheet1").Range("A1").formula2 = 'your formula'


prior to finding the above resolution, I resourced to using the substitute function in Excel (365) to find all @ in my sheet and replaced it with empty ("" - basically deleting it in any formula in the sheet)

this may not be the resolution but I hope I somehow give you a hint :)
 
Upvote 0
Armstrong_N14 said:
I think thats what happens when you use macro to paste formula in a cell. It happened to me just that my code to put a formula in a cell using macro was different.
the @ is automatically added if i use this:

Sheets("Sheet1").Range("A1").value = 'your formula'

but the @ no longer shows if I use:
Sheets("Sheet1").Range("A1").formula2 = 'your formula'


prior to finding the above resolution, I resourced to using the substitute function in Excel (365) to find all @ in my sheet and replaced it with empty ("" - basically deleting it in any formula in the sheet)

this may not be the resolution but I hope I somehow give you a hint :)
Click to expand...
I've tried everything and can't get it to work. Might have to take a different approach
 
Upvote 0
The adding the Formula2 worked for me
Rich (BB code): 
wsFiltered.Range("B5").Formula2 = "=" & formula

Gave me the formula below in B5, which doesn't have the @ symbol
Excel Formula: 
=FILTER(Table1, ISNUMBER(SEARCH(B2, Table1[Order ID]))+ISNUMBER(SEARCH(B2, Table1[Order Date]))+ISNUMBER(SEARCH(B2, Table1[Site]))+ISNUMBER(SEARCH(B2, Table1[SP Name]))+ISNUMBER(SEARCH(B2, Table1[Category Name]))+ISNUMBER(SEARCH(B2, Table1[Ship Via]))+ISNUMBER(SEARCH(B2, Table1[Quantity]))+ISNUMBER(SEARCH(B2, Table1[Order Total])),"No Match")

which spilled across the range fine (below was only random data on the other sheet)
Book1
BCDEFGHI
5106-18-N0341473On SiteLaura CallahanGrains/CerealsRoad481204.718
6106-18-N0441668On SiteNancy DavolioBeveragesRoad40240.654
7106-18-N0541463Off SiteJanet LeverlingCondimentsRoad25456.2565
8106-18-N0641529Off SiteRobert KingDairy ProductsRail14363.5346
9106-18-N0741550On SiteMargaret PeacockCondimentsSea201070.483
10106-18-N0841345Off SiteLaura CallahanBeveragesRoad80316.692
11106-18-N0941667On SiteLaura CallahanCondimentsRoad30557.289
12106-18-N1041340On SiteJanet LeverlingSeafoodSea8239.0808
13106-18-N1141728Off SiteAndrew FullerSeafoodRoad8617.3214
14106-18-N1241732On SiteLaura CallahanSeafoodRail20289.4574
15106-18-N1341702Off SiteJanet LeverlingSeafoodRoad25462.8628
16106-18-N1441326On SiteMargaret PeacockConfectionsAir20256.6596
17106-18-N1541597Off SiteMichael SuyamaSeafoodRoad35518.7912
18106-18-N1641632On SiteLaura CallahanMeat/PoultryRail351311.467
19106-18-N1741570Off SiteJanet LeverlingSeafoodSea21814.188
20106-18-N1841234On SiteSteven BuchananConfectionsAir561171.817
21106-18-N1941477Off SiteSteven BuchananMeat/PoultryRail1004674.274
22106-18-N2041261On SiteAnne DodsworthBeveragesAir1577.5086
23106-18-N2141274On SiteLaura CallahanDairy ProductsSea30619.4304
24106-18-N2241660Off SiteLaura CallahanBeveragesRoad25704.4972
Filtered
Cell Formulas
RangeFormula
B5:I24B5=FILTER(Table1, ISNUMBER(SEARCH(B2, Table1[Order ID]))+ISNUMBER(SEARCH(B2, Table1[Order Date]))+ISNUMBER(SEARCH(B2, Table1[Site]))+ISNUMBER(SEARCH(B2, Table1[SP Name]))+ISNUMBER(SEARCH(B2, Table1[Category Name]))+ISNUMBER(SEARCH(B2, Table1[Ship Via]))+ISNUMBER(SEARCH(B2, Table1[Quantity]))+ISNUMBER(SEARCH(B2, Table1[Order Total])),"No Match")
Dynamic array formulas.
 
Last edited:
Upvote 0
Armstrong_N14 said:
whats the generated formula after the code was run?
Click to expand...
This is the formula it generates: =@FILTER(Table1, ISNUMBER(SEARCH(B2, Table1[Entity Name]))+ISNUMBER(SEARCH(B2, Table1[Entity ID]))+ISNUMBER(SEARCH(B2, Table1[Region]))+ISNUMBER(SEARCH(B2, Table1[District]))+ISNUMBER(SEARCH(B2, Table1[Status]))+ISNUMBER(SEARCH(B2, Table1[New column]))+ISNUMBER(SEARCH(B2, Table1[New column 2])),"No Match").

If I change the -@ with = then it spills all the results. With the -@ it just returns the first cell in the range.
 
Upvote 0
Are you definitely using Formula2 and not Formula?

Rich (BB code): 
wsFiltered.Range("B5").Formula2 = "=" & formula
 
Upvote 0
MARK858 said:
The adding the Formula2 worked for me
Rich (BB code): 
wsFiltered.Range("B5").Formula2 = "=" & formula

Gave me the formula below in B5, which doesn't have the @ symbol
Excel Formula: 
=FILTER(Table1, ISNUMBER(SEARCH(B2, Table1[Order ID]))+ISNUMBER(SEARCH(B2, Table1[Order Date]))+ISNUMBER(SEARCH(B2, Table1[Site]))+ISNUMBER(SEARCH(B2, Table1[SP Name]))+ISNUMBER(SEARCH(B2, Table1[Category Name]))+ISNUMBER(SEARCH(B2, Table1[Ship Via]))+ISNUMBER(SEARCH(B2, Table1[Quantity]))+ISNUMBER(SEARCH(B2, Table1[Order Total])),"No Match")

which spilled across the range fine (below was only random data on the other sheet)
Book1
BCDEFGHI
5106-18-N0341473On SiteLaura CallahanGrains/CerealsRoad481204.718
6106-18-N0441668On SiteNancy DavolioBeveragesRoad40240.654
7106-18-N0541463Off SiteJanet LeverlingCondimentsRoad25456.2565
8106-18-N0641529Off SiteRobert KingDairy ProductsRail14363.5346
9106-18-N0741550On SiteMargaret PeacockCondimentsSea201070.483
10106-18-N0841345Off SiteLaura CallahanBeveragesRoad80316.692
11106-18-N0941667On SiteLaura CallahanCondimentsRoad30557.289
12106-18-N1041340On SiteJanet LeverlingSeafoodSea8239.0808
13106-18-N1141728Off SiteAndrew FullerSeafoodRoad8617.3214
14106-18-N1241732On SiteLaura CallahanSeafoodRail20289.4574
15106-18-N1341702Off SiteJanet LeverlingSeafoodRoad25462.8628
16106-18-N1441326On SiteMargaret PeacockConfectionsAir20256.6596
17106-18-N1541597Off SiteMichael SuyamaSeafoodRoad35518.7912
18106-18-N1641632On SiteLaura CallahanMeat/PoultryRail351311.467
19106-18-N1741570Off SiteJanet LeverlingSeafoodSea21814.188
20106-18-N1841234On SiteSteven BuchananConfectionsAir561171.817
21106-18-N1941477Off SiteSteven BuchananMeat/PoultryRail1004674.274
22106-18-N2041261On SiteAnne DodsworthBeveragesAir1577.5086
23106-18-N2141274On SiteLaura CallahanDairy ProductsSea30619.4304
24106-18-N2241660Off SiteLaura CallahanBeveragesRoad25704.4972
Filtered
Cell Formulas
RangeFormula
B5:I24B5=FILTER(Table1, ISNUMBER(SEARCH(B2, Table1[Order ID]))+ISNUMBER(SEARCH(B2, Table1[Order Date]))+ISNUMBER(SEARCH(B2, Table1[Site]))+ISNUMBER(SEARCH(B2, Table1[SP Name]))+ISNUMBER(SEARCH(B2, Table1[Category Name]))+ISNUMBER(SEARCH(B2, Table1[Ship Via]))+ISNUMBER(SEARCH(B2, Table1[Quantity]))+ISNUMBER(SEARCH(B2, Table1[Order Total])),"No Match")
Dynamic array formulas.
Click to expand...
I get an error on the line of code if I use Formula2.
Code: 
' Add the final part of the formula and set it in the "Filtered" sheet
    formula = formula & ",""No Match"")"
    wsFiltered.Range("B5").Formula2 = "=" & formula
[\code]
 
Upvote 0
...and you are using 365 as per your profile?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
problem with VBA and power query
Replies
8
Views
87
mickeystanford_alumni
M
M
  • Question
WorksheetFunction.CountIf with a range of values as criteria
Replies
5
Views
259
Alex Blakenburg
A
B
  • Question
Excel VBA When new rows are added to table 1 copy the information to table 2
Replies
0
Views
420
BlueHawker42
B
T
  • Question
VBA Graph
Replies
1
Views
270
offthelip
O
S
  • Question
Adding Shapes on every cell based on cell value.
Replies
3
Views
389
duggie33
D

Forum statistics

Threads
1,215,092
Messages
6,123,064
Members
449,090
Latest member
fragment

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top