Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Simple Dim causing a problem.

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

    Default

    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

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

    Default

    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


  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    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

    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

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

    Default

    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

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    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

    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, NateO

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

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

    Default

    Thanks Nate.

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

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
  •