Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Macr: Change Positive Values To Negative

  1. #1
    Board Regular
    Join Date
    Jun 2003
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macr: Change Positive Values To Negative

    I use data on a text file and import this into Excel and use a macro to format. The data lists invoices and credit notes and their dollar value.

    Due to a fault in the program that creates the text file all dollar amounts are shown as positive figures. Credit note amounts should correctly be shown as negative figures.

    The invoice number format is 8 characters beginning with a letter and credit notes begin with a number.

    Is there macro code that will change credit note values to a negative figure?

  2. #2
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try

    Code:
    Sub test()
    Dim rng As Range
    Set rng = Range("d2",Range("d" & Rows.Count).End(xlUp))
    rng.Value = Evaluate(rng.Address & "*-1")
    Set rng = Nothing
    End Sub

  3. #3
    Board Regular
    Join Date
    Jun 2003
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro: Change Positive Values To Negative

    Thanks for this code, Jindon. I have tried it but it changes all values in column D to a negative figure.

    What I need is only the credit notes to be changed to a negative figure and all the invoices to remain unchanged.

    Can you help please?

  4. #4
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    can you post few sample data?

  5. #5
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Possibly try the following adaptation of Jindon's code - note that I have assumed that the invoice number/credit note number is in the column to the immediate left of column D (ie the C col):

    Code:
    Sub test()
    Dim rng As Range, r As Range
    Set rng = Range("d2", Range("d" & Rows.Count).End(xlUp))
    For Each r In rng
    If Left$(r.Offset(, -1), 1) Like "[0-9]" Then r.Value = r.Value * -1
    Next r
    Set rng = Nothing
    End Sub
    Richard Schollar

    Using xl2013

  6. #6
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    try

    Code:
    Sub test()
    Dim rng As Range
    Set rng = Range("d2",Range("d" & Rows.Count).End(xlUp))
    rng.Value = Evaluate(rng.Address & "*-1")
    Set rng = Nothing
    End Sub
    Jindon

    Could you explain why the code does not need a loop (eg to apply to every cell individually) - I was very impressed that your code construct could be applied to a multi-cell range. However, it seems to fail if I attempt to use Evaluate with an Excel function (eg using UPPER) - in this case, the entire range is filled with the first cell's value (as amended by the function I am evaluating).

    Thank you!
    Richard Schollar

    Using xl2013

  7. #7
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Richrad

    That's true, because it is like array formula, but not exactly, and does not work properly with non-array functions.

    Code:
    Range("a1").Value = Join(Evaluate("Transpose(A2:A7)")," ")
    does work.

    It may useful in some occasions...

  8. #8
    Board Regular mortgageman's Avatar
    Join Date
    Jun 2005
    Posts
    2,015
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Richrad

    That's true, because it is like array formula, but not exactly, and does not work properly with non-array functions.

    Code:
    Range("a1").Value = Join(Evaluate("Transpose(A2:A7)")," ")
    does work.

    It may useful in some occasions...
    In the spirt of no question is a dumb question: How does it (the formula) "know" to be like, but not exactly, an array function?
    Gene, "The Mortgage Man", Klein
    See all my Mishegas Videos
    NEW: MY INTERNET RADIO SHOW:
    http://www.blogtalkradio.com/mortgageman

  9. #9
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Gene

    I suspect that this depends on the function concerned (ie is it coded to accept arrays - as in my example, Upper obviously isn't) and also then on how the arguments to the function are passed (ie in a range or as a discrete single range/value). Sorry, above explanation probably hasn't enlightened you much...
    Richard Schollar

    Using xl2013

  10. #10
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK

    Put this way.

    Evaluate method;

    Range("a1:a10").Value = Evaluate("A1:A10+100")

    used like array formula, it can hold individual calculated results for respective cells, however it doesn't work when non-array function comes in.
    That's why I said "Not exactly".

    As Richard experienced, it only returns the first result to the entire range.

    I don't know if it works with "Sumproduct", "DSum" etc, because I'm not good at formulas, you know?

    And also Evaluate method can create 1based 2 dim array like

    myList = [{"a","b","c","d";1,2,3,4}]

    [ ] are used for Evaluate.

    FYI [a1] = 1 is Evaluate("a1").Value = 1

    hope this helps

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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