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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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 Question
Split Excel Workbook to separate .xlsx files for each unique value in Column A, also copy worksheet for Drop Down reference
Replies
9
Views
456
Alex Blakenburg
A
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
193
krunal123
K
M
  • Question Question
problem with VBA and power query
Replies
8
Views
497
mickeystanford_alumni
M
D
  • Solved
Merge files and keep source formatting.
Replies
9
Views
339
Alex Blakenburg
A
H
  • Solved
Query not pulling right info when running in VBA
2
Replies
13
Views
396
Holley
H

Forum statistics

Threads
1,223,948
Messages
6,175,567
Members
452,652
Latest member
eduedu

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