Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Training (2003)

    Hi,
    With a lot of help from Hans (amongst others), I have recently designed a few 'matching models' that match debits and credits in reconciliations. These have been a massive improvement to the current processes. I need to create a few others for different reconciliation scenario's, which I am attempting using snippets of previous code, though I am getting a little confused. I'm relatively new to VBA but am learning fast. The attached scenario is pretty different to previous one's, and the help that I am looking for is the best way to approach the code. Please, I need to write this stuff myself, so to achieve a greater understanding, I would appreciate guidance as to the best approach!

    2 tabs - debits and credits, raw data being in columns A-I.
    Concatenate formula in K
    First match in column L.
    If no match in L, different concatenate in M.
    If concatenate in M, second match in N.
    If no match in N, different concatenate in O.
    If concatenate in O, third match in P or overall No Match.

    In other words, the second match is only required if there was no first match, the third if there was no second.....
    Matches in each case occur when there are equal quantities of identical debits and credits based on each concatenate. Effectively, I want to recreate the formula's in K-P with code.

    The code module 1 is where I am at currently, any pointers greatfully received!
    In what order would the looping need to be set out, or does that matter?

    Many Thanks
    Nath
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Training (2003)

    If you want to start in row 3, you can simply use c1 = 3 (and d1 = 3)

    You can concatenate values in code using the & operator, e.g.

    ws1.Range("K" & lngRow) = ws1.Range("A" & lngRow) & " - " & ws1.Range("D" & lngRow) & " - " & ws1.Range("F" & lngRow)

    You can also use worksheet functions such as COUNTIF in code - you've seen examples in a recent thread (<post:=705,573>post 705,573</post:>)

    You should enter the values in column K in both sheets first, then those in column L (because they depend on column K being filled), then those in column M, etc.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Training (2003)

    Hans, Getting there (slowly) :-)
    The first section executes (credits) but the debits don't?

    Set ws1 = Worksheets("CREDITS")
    Set ws2 = Worksheets("DEBITS")

    c1 = 3 'First Credit Data Row number
    c2 = ws1.Cells(ws1.Rows.Count, 6).End(xlUp).Row 'Last Credit Data Row number

    d1 = 3 'First Debit Data Row number
    d2 = ws2.Cells(ws2.Rows.Count, 6).End(xlUp).Row 'Last Debit Data Row number

    ' Loop through credit rows
    For lngRow = c1 To c2

    ' Populate credit column K

    If Range("B" & lngRow) = "" Then
    Range("K" & lngRow) = "="""""
    Else
    Range("K" & lngRow) = Range("A" & lngRow) & " - " & Range("D" & lngRow) & " - " & Range("F" & lngRow)
    End If

    Next lngRow

    ' Loop through debit rows
    For lngRow = d1 To d2

    ' Populate debit column K

    If Range("B" & lngRow) = "" Then
    Range("K" & lngRow) = "="""""
    Else
    Range("K" & lngRow) = Range("A" & lngRow) & " - " & Range("D" & lngRow) & " - " & Range("F" & lngRow)
    End If

    Next lngRow

    Thanks,
    Nath

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Training (2003)

    If you use Range without explicitly prefixing it with a worksheet, it will refer to the currently active sheet, which may not be the sheet you want to populate. So for the credits sheet, you should consistently use ws1.Range(...) and for the debits sheet ws2.Range(...)

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Training (2003)

    So, I presume that it is better to declare the range as a variable? r1 = c1 to c2 ? But then I can't start as "For IngRow = r1" ?

    I know (as you said) I can use ws1.range throughout the code, but is that not more complex than it needs to be? Apologies for my brain block!!

    Maybe I'mbarking upthe wrong tree here. :-()()()

    Thanks,
    Nath

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Training (2003)

    Using

    For lngRow = c1 To c2
    ...
    Next lngRow

    is fine - no need to change that. But you must use ws1.Range("B" & lngRow) instead of Range("B" & lngRow) if you want to be certain that you're referring to the credits sheet, and ws2.Range("B" & lngRow) for the debits sheet. Similar for the other cells, of course. That's not making things more complicated than they need to be, it's essential to ensure that the code does what you want.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Training (2003)

    Hans,
    The first column (K) is now fine, but I am struggling with column L. The code executes fine but gives incorrect results (all read match 1). I can't see where I am going wrong?

    Thanks
    Nath
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Training (2003)

    Code such as

    For lngRow = c1 To c2
    CRcount1 = ...
    Next lngRow

    is useless because it calculates hundreds of values in a loop but it doesn't do anything with those values, they are discarded just as fast as they're calculated. You must calculate values *AND* do something with them. For example for column L on the credits sheet:

    For lngRow = c1 To c2
    CRcount1 = Application.WorksheetFunction.CountIf(ws1.Range("K " & c1 & ":K" & c2), ws1.Range("K" & lngRow))
    DRcount1 = Application.WorksheetFunction.CountIf(ws2.Range("K " & d1 & ":K" & d2), ws1.Range("K" & lngRow))
    If CRcount1 = DRcount1 Then
    ws1.Range("L" & lngRow) = "MATCH 1"
    Else
    ws1.Range("L" & lngRow) = "NO MATCH"
    End If
    Next lngRow

    Note that the second argument to CountIf uses a cell on ws1 in both cases, just like the formulas you originally had in column L.

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Training (2003)

    Cooking on Gas!!

    Column N:

    How do I express: If credit column L = NO MATCH > AND <

    If CRcount2 = DRcount2 Then
    ws1.Range("N" & lngRow) = "MATCH 2"
    Else
    ws1.Range("N" & lngRow) = "NO MATCH"
    End If


    And on that note, it should be project complete!! :-)

    Many Thanks
    Nath

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Training (2003)

    Perhaps

    If ws1.Range("L" & lngRow) = "NO MATCH" Then
    If CRcount2 = DRcount2 Then
    ...
    Else
    ...
    End If
    Else
    ...
    End If

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Training (2003)

    Bingo!! All done and dusted. Thanks again for all your help!

    Nathan

Posting Permissions

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