Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: NateO

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nate,
    I tried your suggestion for the following:

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 5/8/2002 by Mike Zaccardo
    '
    Dim cell As Range

    'where I replaced .Range("M2:M" & lastrow) = "If(RC[-2]<0,RC*-1,RC)" with

    For Each cell In Range("m1:m" & lastrow)
    If cell.Offset(, -2).Value < 0 And cell.Value > 0 _
    Then cell = -cell.Value
    Next cell
    End Sub

    On the coding above I'm getting a DEBUG msg on "For Each cell....."

    Trying to change the value in a cell to a negative number if the total sales 2 cells to the left is negative.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Add the End If and see if it works:

    Then cell = -cell.Value
    next cell
    >>End IF<<
    End Sub

    James

  3. #3

    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your macro doesn't include any value for "Lastrow".

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Zac, in your original post, you had:

    Code:
    Dim lastrow As Long 
    Dim UsedRng As Range, UsedCell As Range 
    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    Did you remove these statements, if so, we'll need to add them back, let XL know when the last cell in the range to be tested is.

    No matter, allow me to compile:

    Code:
    Dim lastrow As Long, cell As Range
    Dim UsedRng As Range, UsedCell As Range
    'other code
    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    For Each cell In Range("m1:m" & lastrow)
    If cell.Offset(, -2).Value < 0 And cell.Value > 0 _
    Then cell = -cell.Value
    Next cell
    'other code
    Incidentally, as a bean counter, I'm curious, if you get a return, the quantity is positive, wouldn't you want the $ amount negative? Thus telling you have a credit (versus debit with positive numbers) to your cash account?

    No matter. Your other code looked to be in good shape (at the time...). Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-08 16:56 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    NateO,

    All our data is extracted from our MRP module.
    The columns run from a:aa, and the rows vary from 200+.
    The bookkeeper then spends about 3-5 hours
    deleting and adding what is and isn't needed.
    There is a column of "Total Sales" for line items, and a few turn up negative. There is
    a "Qty Shipped" column, but theses numbers get brought over as "positive".
    Why I don't know.

    Being self taught with XL, I always try to learn something new every day. So I decided to try and learn how to record macro's, with the intent of automating what they need done in Accounting at time of extraction.

    Needless to say the first month end closing ran great. But the previous recording left me with a couple of minor problems, which caused some lines not to be used correctly.
    One of them was the use of the Data Filter, and the one mentioned above (changing the Qty Shipped line that's associated with a Negative Total Sales number.

    I hope your idea does the trick.

    The Data Filter is another headache.
    I use the D/F to show a salesmans Commission Code (ie: 026). I'm trying to figure out
    how to go from B1 to the next line down.
    The visible lines vary each month (39:80)
    (51:75), etc . When I use the down arrow in my recording, it initially set up a scenario of goto (39,0) as example. If my data runs from (30:50), it jumps over (30:38).

    Sorry to ramble on. That's it in a nutshell.

    Thanks for your help, I hope the last suggestion works (using DOWN instead of using the down arrow).

    Thanks,
    Zac


  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not necessarily following, but I think the code above will take care of column M's data. with respect to:

    I'm trying to figure out
    how to go from B1 to the next line down
    Try:
    Code:
    [b1].Offset(1).Select 'change select to what you want it to do (e.g., copy)

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
  •