VBA Training (2003)

Topic

New Reply

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

Reply | Quote

Replies

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)

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.

Reply | Quote

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

Reply | Quote

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(…)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Perhaps

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

Reply | Quote

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

Nathan

Reply | Quote