NateO

[Zac]

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.

 

[James]

Add the End If and see if it works:

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

James

 

[Amds]

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

 

[NateO]

Hi Zac, in your original post, you had:

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:

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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-08 16:56

 

[Zac]

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

 

[NateO]

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:

[b1].Offset(1).Select 'change select to what you want it to do (e.g., copy)