+ Reply to Thread
Results 1 to 6 of 6

How to remove the @ sign from VBA formulas

  1. #1
    Forum Contributor
    Join Date
    04-03-2009
    Location
    Anchorage, Alaska, USA
    MS-Off Ver
    Office 365
    Posts
    102

    How to remove the @ sign from VBA formulas

    Hi,

    Sorry if this is already covered. But when I search the forum for "@" I get nothing; and when I search for "at" I get practically everything.
    I have this code, and it's working great except for the "UNIQUE" formula. It works, but it doesnt list, you know what I mean? It stops at 1 result instead of listing all the unique results. Basically a unique list will be a list of unique dates, not just 1 date.

    For the code below, an @ sign preceeds the UNIQUE formula, like so :
    Formula Bar shows this:
    =@UNIQUE(FILTER($F$11:INDIRECT("F"&(COUNTA($B$11:$B$2500))+11-1),$C$11:INDIRECT("C"&(COUNTA($B$11:$B$2500))+11-1)="P"))


    I dont want that @-sign cause it limits the formula, like I said, to 1 result :


    Please Login or Register  to view this content.

    When I apply the UNIQUE formula in a fixed cell via a macro, like so :


    Please Login or Register  to view this content.


    It works - I get this in the Formula Bar:
    =UNIQUE(FILTER($R$11:INDIRECT("R"&(COUNTA($B$11:$B$2500))+11-1),$B$11:INDIRECT("B"&(COUNTA($B$11:$B$2500))+11-1)=" WOTRAN ") )
    What's this UNIQUE formula do? Whenever column B = " WOTRAN " (means 'writeoff' in our company) then give me the cell info in column R, then further pair that info down so only unique values from column R are listed. And do this for the data between rows 11 and the bottom of the list as determined by column B (column B was chosen cause it always contains data to the bottom of the list whereas other columns may contain blanks here and there).

    Notice no @ preceding the "=UNIQUE...".


    When I use the longer macro at top, the formula bar shows this:
    =@UNIQUE(FILTER($F$11:INDIRECT("F"&(COUNTA($B$11:$B$2500))+11-1),$C$11:INDIRECT("C"&(COUNTA($B$11:$B$2500))+11-1)="P"))
    What's this UNIQUE formula do? Whenever column C = "P" then give me the cell info in column F, then further pair that info down so only unique values from column F are listed. And do this for the data between rows 11 and the bottom of the list as determined by column B (column B was chosen cause it always contains data to the bottom of the list whereas other columns may contain blanks here and there).

    Granted the formulas are a little different only because I'm looking for different data. But I think the @-error, which is my concern here, stems from the way I'm producing the formula. I think the Worksheet.Range.Cells method in the first code (if I'm using that terminology correctly, I'm a noob in that respect) is somehow causing a different outcome vs. the Range.ActiveCell stuff in the second code. That's the only thing I can see.

    Why am I using different methods? Cause the location of the summary information is going to show at the bottom of an Excel list, which may be 20, 30, 50, 100, 200 rows long. So I have to keep the row variable, and then produce my summary relative to the bottom of the list. If there's 60 rows then the variable NR will be 63 - my summary start row.
    Thanks.

    Thoughts?
    Last edited by akedm; 04-07-2021 at 03:29 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to remove the @ sign from VBA formulas

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    04-03-2009
    Location
    Anchorage, Alaska, USA
    MS-Off Ver
    Office 365
    Posts
    102

    Re: How to remove the @ sign from VBA formulas

    This has been done - my original post has been modified.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,886

    Re: How to remove the @ sign from VBA formulas

    Use Formula2, not Value, when assigning the formula to the cells.
    Rory

  5. #5
    Forum Contributor
    Join Date
    04-03-2009
    Location
    Anchorage, Alaska, USA
    MS-Off Ver
    Office 365
    Posts
    102

    Re: How to remove the @ sign from VBA formulas

    Why would that work?? But it works!
    Thank you.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to remove the @ sign from VBA formulas

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Remove sign and subtract in the same formula
    By mss90 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-06-2020, 04:30 AM
  2. Remove $ sign and space before and after in range
    By smartbuyer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-23-2016, 06:12 AM
  3. Remove borders and x sign on a userform through VBA
    By Sahonero in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-22-2014, 06:56 AM
  4. [SOLVED] Want to remove $ sign Form A4:A50
    By HaroonSid in forum Excel General
    Replies: 7
    Last Post: 04-21-2014, 12:52 AM
  5. Remove dollar sign in a formula
    By jolenec in forum Excel General
    Replies: 4
    Last Post: 03-14-2014, 10:02 PM
  6. [SOLVED] Remove - sign from columns
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-30-2013, 10:05 AM
  7. Remove a sign from a number
    By Monica85 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-28-2013, 01:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1