Simple Dim causing a problem.

Zac

Well-known Member
Joined
Feb 20, 2002
Messages
796
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0
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
 
Upvote 0
I narrowed this down to column M. You could try this:

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Just add the following to your variable defintions (dim etc....)

dim cell as range
and replace

Code:
'.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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
wave.gif

This message was edited by NateO on 2002-05-03 20:54
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top