Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Show Plus Sign On Numbers Greater Than X

  1. #1
    Join Date
    9th August 2008
    Posts
    6

    Show Plus Sign On Numbers Greater Than X

    I'm trying to write a macro in Excel that would change any number greater than 10 in a spreadsheet to say "+10"

    Please let me know. Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,093

    Re: Conditional Statements

    Wrong move. It will change the number into a text string and you can no longer use the number for calculations without going to trouble to strip off the "+" sign.

    Why do you need this?

    ADDED BY ADMIN

    Custom Format the cells as;

    [>9]+General;General
    Last edited by Dave Hawley; August 9th, 2008 at 09:00.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    9th August 2008
    Posts
    6

    Re: Conditional Statements

    my worksheet looks like this:
    column A s m l xl
    men's jacket 100 150 200 9

    could it say: >10 >10 >10 9

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,093

    Re: Conditional Statements

    Post a sample workbook and we'll take a look at it.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  5. #5
    Join Date
    9th August 2008
    Posts
    6

    Re: Conditional Statements

    here is a sample:

    S M L XL XXL

    COMP ZIP JKT CR 40 57 67 58 10
    COMP ZIP JKT GR 4 -5 7 14 3

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,093

    Re: Conditional Statements

    Posting a sample workbook means to upload a copy. File size is limited to 48K.

    When posting scroll down a bit until you see an "Additional Options" section that lets you manage attachments. You should be able to take it from there.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  7. #7
    Join Date
    9th August 2008
    Posts
    6

    Re: Conditional Statements

    Please let me know if you can open the following Apparel ATS spreadsheet. Thanks.
    Apparel ATS.xls

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,093

    Re: Conditional Statements

    I'm trying to write a macro in Excel that would change any number greater than 10 in a spreadsheet to say "+10".
    I don't want my customers to know that I have, say 100 of an item. We just want to let them know we have, say enough product to fill their order
    Unless you're required to let them view your inventory records, can't you just confim to them you have the stock on-hand to meet their order requirements?

    I assume you are refering to the totals column. This will place a "10+" in the column next to the totals.

    VB:
    Sub Show10() 
         
        Dim c As Range 
         
        With Sheet1 
             
            For Each c In Range("J5", Range("J65536").End(xlUp)) 
                If c.Value > 10 Then 
                    c.Offset(0, 1).Value = "10+" 
                End If 
            Next c 
             
        End With 
         
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  9. #9
    Join Date
    9th August 2008
    Posts
    6

    Re: Conditional Statements

    actually, we need the individual sizes to show the 10+ too (in addition to the totals)
    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,093

    Re: Conditional Statements

    Replace the code in the workbook with this:

    VB:
    Sub Show10() 
         
        Dim c As Range, c2 As Range 
         
        With Sheet1 
             
            For Each c In Range("J5", Range("J65536").End(xlUp)) 
                If c.Value > 10 Then 
                    c.Offset(0, 1).Value = "10+" 
                End If 
            Next c 
             
            For Each c2 In Range("D3", Range("D3").End(xlToRight)) 
                If c2.Value > 10 Then 
                    c2.Offset(1, 0).Value = "10+" 
                End If 
            Next c2 
             
             
        End With 
         
    End Sub 
    
    
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Find Replace Numbers Greater Than & Less Than 2 Numbers
    By Psycho-linguist in forum EXCEL HELP
    Replies: 2
    Last Post: June 11th, 2008, 03:49
  2. Join 2 Cells & Show Equal Sign In Cell
    By devil1980 in forum EXCEL HELP
    Replies: 3
    Last Post: January 17th, 2008, 17:36
  3. swapping a numbers sign
    By sdj1704 in forum EXCEL HELP
    Replies: 5
    Last Post: September 16th, 2005, 01:29
  4. Show positive numbers with '+' sign
    By aldredd in forum EXCEL HELP
    Replies: 2
    Last Post: October 18th, 2004, 09:40

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
  •  
porno