Announcement

Collapse
No announcement yet.

Show Plus Sign On Numbers Greater Than X

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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, 09:00.
    AAE
    ----------------------------------------------------

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

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              Re: Conditional Statements

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

              Comment


              • #8
                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.

                Code:
                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
                AAE
                ----------------------------------------------------

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

                Comment


                • #9
                  Re: Conditional Statements

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

                  Comment


                  • #10


                    Re: Conditional Statements

                    Replace the code in the workbook with this:

                    Code:
                    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

                    Comment

                    Working...
                    X