Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Nov 2001
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro to negate (97 no SR)

    Hi all

    I need a macro that will do the following formula...

    =IF(C2="Returns",E2*-1,IF(C2="Credit Notes",E2*-1,E2))

    On the attached spreadsheet.

    The tricky part is next time I get this sheet it will have a different amount of rows.

    Once the formula is applied I need to subtotal on the customer name and then show the summary level that only shows the customer name and amount.

    Any help is appreciated.

    Ian
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to negate (97 no SR)

    Does this do what you want?

    <pre>Public Sub SetNeg()
    Dim lLastRow As Long, I As Long
    lLastRow = Range("A1").Offset(Worksheets("Sheet1").UsedRange. Row + _
    Worksheets("Sheet1").UsedRange.Rows.Count, 0).End(xlUp).Row
    With Worksheets("Sheet1").Range("C1")
    For I = 0 To lLastRow
    If .Offset(I, 0).Value = "Returns" Or .Offset(I, 0).Value = "Credit Notes" Then
    .Offset(I, 2).Value = .Offset(I, 2).Value * -1
    End If
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Nov 2001
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to negate (97 no SR)

    Thanks for the assistance this looks like it should do the trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •