Simple Dim causing a problem.

[Zac]

In Col "K" I have Extended Sales.
In Col "M" I have Qty Shipped.

When the $ in "M" is a negative,
I need to turn the number in "M" negative.

Here's Coding I used:

'The DIM code that follows Formats "O", "P", & "R".
Dim lastrow As Long
Dim UsedRng As Range, UsedCell As Range

With ActiveSheet
.UsedRange 'Resets the range (so I'm told)

'The NExt Line Formats "M".
.Range("M2:M" & lastrow) = "=IF(RC[-2]<0,RC*-1,RC)"
End With

End Sub

Result: All data in "M" turns to 0.
and the Cell contents in M2 now reads =IF(K2<0,M2*-1,M2) !!!!!!!!!!!

Anyone have a clue where I goofed?

ZAc

[Al Chara]

Confusing...can you try to explain what you want to do clearer.

[Zac]

For AL,

For arguments sake:
In Column "K" we have 100 rows of Total Dollars. A few may be negative depending on REturns, etc.

In Col, M, I have my "Shipped Qty" (none are negative numbers.

In "N", we have Cost per unit,
and in "O" we have extended Dollars Shipment $$ for this line).

If any number in "K is (Negative) I need to correct the shipped qty in "M" to a (Negative Number) so that the extended shipping dollars is accurate.

"M" "N" "O"
(Qty Shipped) x (Unit Cost) = Ext Shipping $$

Can I use a Formula in the code as previously shown?
I think it may be wrong.

How's that?

Zac

[Al Chara]

Try the following:

Dim UsedRng As Range
Set UsedRng = ActiveSheet.UsedRange
For i = 2 To UsedRng.Rows.Count
If Cells(i, 11) < 0 Then
Cells(i, 15).Formula = "=(-" & Cells(i, 13).Address & ")*" & Cells(i, 14).Address
Else
Cells(i, 15).Formula = "=(" & Cells(i, 13).Address & ")*" & Cells(i, 14).Address
End If
Next

edit as needed

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-05-03 13:12 ]

[NateO]

I narrowed this down to column M. You could try this:

Sub neagatron()
Dim cell As Range, lsrw As Long
lsrw = [m65536].End(xlUp).Row
For Each cell In Range("m1:m" & lsrw)
If cell.Offset(, -2).Value < 0 And cell.Value > 0 _
Then cell = "-" & cell.Value
Next cell
End Sub

[Zac]

AL,

Here's what I was using in my MACRO.
All of it worked great.

'The DIM code that follows Formats "O", "P", & "R".
Dim lastrow As Long
Dim UsedRng As Range, UsedCell As Range

With ActiveSheet
.UsedRange 'Resets the range (so I'm told)

lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
'The Next Line Formats D with EFP.
.Range("D2:D" & lastrow) = "EFP"
'The NExt Line Formats "M".
'.Range("M2:M" & lastrow) = "=IF(RC[-2]<0,RC*-1,RC)"
'The Next Line Formats "R".
.Range("O2:O" & lastrow) = "=RC[-2]*RC[-1]"
'The Next Line Formats "P".
.Range("P2:P" & lastrow) = "=RC[-1]*RC[+2]"
'The Next Series Formats "R".
Set UsedRng = .Range("R2:R" & lastrow)
For Each UsedCell In UsedRng
UsedCell = UsedCell * 0.01
Next UsedCell

End With


I thought I could add one simple line that would do the trick. Can I add the line using an "IF statement"? If not I won't bother.

ZAc

[Jay Petrulis]

Hi,

The line:

.Range("M2:M" & lastrow) = "=IF(RC[-2]<0,RC*-1,RC)"

is adding a circular reference to the worksheet, as the function calls itself.

You will have to change the data rather than writing the formula to change the data.

Also, you might consider adjusting the formula in the calculated column to handle this.

[NateO]

Just add the following to your variable defintions (dim etc....)

dim cell as range
and replace

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

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-03 20:54 ]

[Zac]

Thanks Nate.

I'll try it and let you know how it works.