Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macros for Debit and Credits (Excel 2003)

    Hi

    I have a file where I have Debit amounts in Column L and Credit amounts in Column M.

    What I need to do is Copy any value in Column L into Column M and vice versa copy any value in Column M into Column L.

    But the trciky part is once I copy to the values to the new column (either col M or L) I need to paste that value one row down.

    Here is a sample of what I need to do:

    BEFORE -

    Purchase Invoic 509 WJ 1667N 33410 27.59

    Purchase Invoic 509 WJ 1667N 34223 22.25

    Purchase Invoic 509 WJ 1667N 34223 38.65

    TO RECORD DEPRE 509 WJ 0000N 38310 101.17

    TO RECORD DEPRE 509 WJ 0000N 38310 268.28

    Purchase Invoic 509 WJ 0000N 38645 12


    Purchase Invoic 509 WK 0164N 33220 61.2

    Purchase Invoic 509 WK 1137N 33220 91.34

    Purchase Invoic 509 WK 1137N 33220 95.22

    AFTER:

    Purchase Invoic 509 WJ 1667N 33410 27.59
    Purchase Invoic 509 WJ 1667N 33410 27.59
    Purchase Invoic 509 WJ 1667N 34223 22.25
    Purchase Invoic 509 WJ 1667N 34223 22.25
    Purchase Invoic 509 WJ 1667N 34223 38.65
    Purchase Invoic 509 WJ 1667N 34223 38.65
    TO RECORD DEPRE 509 WJ 0000N 38310 101.17
    TO RECORD DEPRE 509 WJ 0000N 38310 101.17
    TO RECORD DEPRE 509 WJ 0000N 38310 268.28
    TO RECORD DEPRE 509 WJ 0000N 38310 268.28
    Purchase Invoic 509 WJ 0000N 38645 12
    Purchase Invoic 509 WJ 0000N 38645 12
    Purchase Invoic 509 WK 0164N 33220 61.2
    Purchase Invoic 509 WK 0164N 33220 61.2
    Purchase Invoic 509 WK 1137N 33220 91.34
    Purchase Invoic 509 WK 1137N 33220 91.34
    Purchase Invoic 509 WK 1137N 33220 95.22
    Purchase Invoic 509 WK 1137N 33220 95.22

    NOTE: I already have a macro that will copy me down the first five columns.

    Thank you.

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

    Re: Macros for Debit and Credits (Excel 2003)

    The Lounge formatting of your text makes it very unclear what you are trying to do. Could you attach a workbook that shows what you have on one sheet and what you want on a second sheet? That way we can use that workbook to test the code.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros for Debit and Credits (Excel 2003)

    Sure please see the attached.

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

    Re: Macros for Debit and Credits (Excel 2003)

    First, a comment about your workbook. You have code in the module behind the worksheets that is not event routine code. This is not the place to put normal code. You should use Module command from the Insert menu in the VBE to add a module to the workbook and put normal code there. The modules behind the worksheets should be reserved for event routines. These modules are special and normal code may not work properly there.

    Does this code do what you want?

    <code>
    Public Sub ArangeData()
    Dim I As Long
    For I = 0 To Range("A65536").End(xlUp).Row - 1 Step 2
    Range("A2:K2").Offset(I, 0).Copy Destination:=Range("A2").Offset(I + 1)
    If Trim(Range("M2").Offset(I, 0).Value) <> "" Then
    Range("L2").Offset(I + 1, 0).Value = Range("M2").Offset(I, 0).Value
    Range("M2").Offset(I + 1, 0).Value = ""
    Else
    Range("M2").Offset(I + 1, 0).Value = Range("L2").Offset(I, 0).Value
    Range("L2").Offset(I + 1, 0).Value = ""
    End If
    Next I
    End Sub
    </code>
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros for Debit and Credits (Excel 2003)

    Hi LC,

    I did as you instructed, however it did not work. The error message that is reciecved is compile error: syntax error. i think there is a problem with this line in the macro:

    If Trim(Range("M2").Offset(I, 0).Value) <> "" Then

  6. #6
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros for Debit and Credits (Excel 2003)

    Hello anyone able to provide assistance?

    Thank you

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macros for Debit and Credits (Excel 2003)

    It works for me. I have no problem with any lines

    Steve

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

    Re: Macros for Debit and Credits (Excel 2003)

    You really need to be more patient. The people answering questions here are volunteers, not paid consultants.

    The attached file is the workbook you uploaded with my macro inserted. I tested it and it does work.
    Legare Coleman

  9. #9
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros for Debit and Credits (Excel 2003)

    Ok i got the macro to run but not work (I was copying the email response which somehow changed the code).

    Anyway attached is the output after I ran the macro, which is not what I was trying to do (please see orginal email with attachment).

    Thank you

  10. #10
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros for Debit and Credits (Excel 2003)

    Hi Legare,
    I recognize that you are all volunteers and I appreciate the help. It's just that in the past I have posted things and gotten answers almost immediately! (that's why this place ROCKS!!! ) So I guess I have been spoiled - my apologies.

    Regards

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

    Re: Macros for Debit and Credits (Excel 2003)

    It doesn't work because this workbook is NOT the same as the first one you uploaded. This workbook has a blank row between the header row and the first row of data. The first workbook did not. Either delete this row or change the macro to:
    <code>
    Public Sub ArangeData()
    Dim I As Long
    For I = 0 To Range("A65536").End(xlUp).Row - 1 Step 2
    Range("A3:K3").Offset(I, 0).Copy Destination:=Range("A3").Offset(I + 1)
    If Trim(Range("M3").Offset(I, 0).Value) <> "" Then
    Range("L3").Offset(I + 1, 0).Value = Range("M3").Offset(I, 0).Value
    Range("M3").Offset(I + 1, 0).Value = ""
    Else
    Range("M3").Offset(I + 1, 0).Value = Range("L3").Offset(I, 0).Value
    Range("L3").Offset(I + 1, 0).Value = ""
    End If
    Next I
    End Sub
    </code>
    Legare Coleman

  12. #12
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros for Debit and Credits (Excel 2003)

    UREEEKA!!!!

    It works....many thanks!

Posting Permissions

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