Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    use next row? (97, 2000)

    I'm working on mechanising the treatment of payments received shown on an online bank statement.
    This involves identifying the payments to the correct people, and crediting the amounts to either 'increase my savings' or 'make this a loan repayment', or a bit of each, according to the member's known intentions.
    I'm stuck on the last one.
    The snippet attached illustrates the problem (opportunity?).
    I suspect the solution needs VBA, but as the only sort I know is the 'point & shoot' sort generated by the macro recorder, a link to a previous posting describing in sordid detail how to create a new module, project or whatever would be most helpful.
    Thanks in advance.

  2. #2
    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: use next row? (97, 2000)

    How about some code like this:

    <pre>Option Explicit
    Sub Opportunity()
    Dim lLastRow As Long
    Dim lRowSource As Long
    Dim lRowDest As Long
    Dim DestStart As Range
    Dim iCol As Integer
    Dim iColDate As Integer
    Dim iColCheck As Integer
    Dim x As Integer

    iCol = 7
    iColDate = 1
    iColCheck = 12
    Set DestStart = Range("n3")
    lLastRow = Cells(65536, iCol).End(xlUp).Row
    lRowDest = 0

    For lRowSource = 3 To lLastRow
    For x = 2 To 3
    If Cells(lRowSource, iCol + x) <> 0 Then
    DestStart.Offset(lRowDest, 0) = _
    Cells(lRowSource, iCol)
    DestStart.Offset(lRowDest, 1) = _
    IIf(x = 2, 1, 3)
    DestStart.Offset(lRowDest, 2) = _
    Cells(lRowSource, iCol + x)
    DestStart.Offset(lRowDest, 3) = _
    Cells(lRowSource, iColDate)
    Range(DestStart.Offset(lRowDest, 0), _
    DestStart.Offset(lRowDest, 3)).Font.Color = vbBlack
    If Cells(lRowSource, iColCheck).Value = "NO" Then
    Range(DestStart.Offset(lRowDest, 0), _
    DestStart.Offset(lRowDest, 3)).Font.Color = vbRed
    End If
    lRowDest = lRowDest + 1
    End If
    Next
    Next
    End Sub</pre>


    Goto VB (alt-F11)
    insert module
    copy the code above and paste it into the code pane.

    Change ranges as appropriate

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use next row? (97, 2000)

    Mega thanks, Steve, will work on this route at the weekend and come back if I need to.

Posting Permissions

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