vba loop with conditional ifs - help needed

[PAULGOLD]

cheque book Bank Data
Date cheque no amount matching cheque in bank date cheque no amount
01/10/2009 692201 40000 no match 01/10/2009 692203 23000
02/10/2009 692202 50000 no match 05/10/2009 692201 40000
03/10/2009 692203 23000 no match 06/10/2009 692205 51000
04/10/2009 692204 29000 no match 09/10/2009 692202 50000
05/10/2009 692205 51000 no match 08/10/2009 692204 29000
06/10/2009 692206 35000 no match 11/10/2009 692206 35000
07/10/2009 692207 15000 no match 08/10/2009 692208 1
08/10/2009 692208 20010 no match
09/10/2009 692209 5130 no match 07/10/2009 692207 15000
10/10/2009 692210 12 no match 13/10/2009 692213 1500
11/10/2009 692211 1500 no match
12/10/2009 692212 1500 no match
13/10/2009 692213 1500 no match


hi I am trying to write code for column D using a loop by comparing column B to column F. where the two do not match i want to insert " not match", if the two match but the amounts in columns C and G I want to give Ref number and state that the "refs match" .
if they refs in column B and F and amounts match in columns C and G I want to write the reference only.


my VBA code so far is below but is not quite working - and is only producing "no match":

Sub bankreconciliation()
Dim x, y As Integer

For x = 3 To Cells(Rows.Count, 1).End(xlUp).Row
For y = 3 To Cells(Rows.Count, 1).End(xlUp).Row

If Cells(x, 2) <> Cells(y, 6) Then
Cells(x, 4) = " no match"

ElseIf Cells(x, 2) = Cells(y, 6) Then
Cells(x, 4) = Cells(y, 6) & " REFS MATCH"

ElseIf Cells(x, 2) = Cells(y, 6) And Cells(x, 3) = Cells(y, 7) Then
Cells(x, 4) = Cells(y, 6)

End If

Next y
Next x

End Sub

 

[venkat1926]

the problem is not clear.

the data is like this from row no. 2 and not row no. 3

Excel Workbook
	A	B	C	D	E	F	G
1	Date	cheque no	amount	matching cheque in bank	date	cheque no	amount
2	1/10/2009	692201	40000	no match	1/10/2009	692203	23000
3	2/10/2009	692202	50000	no match	5/10/2009	692201	40000
4	3/10/2009	692203	23000	no match	6/10/2009	692205	51000
5	4/10/2009	692204	29000	no match	9/10/2009	692202	50000
6	5/10/2009	692205	51000	no match	8/10/2009	692204	29000
7	6/10/2009	692206	35000	no match	11/10/2009	692206	35000
8	7/10/2009	692207	15000	no match	8/10/2009	692208	1
9	8/10/2009	692208	20010	no match
10	9/10/2009	692209	5130	no match	7/10/2009	692207	15000
11	10/10/2009	692210	12	no match	13/10/2009	692213	1500
12	11/10/2009	692211	1500	no match
13	12/10/2009	692212	1500	no match
14	13/10/2009	692213	1500	no match
Sheet1

quote
column B to column F. where the two do not match i want to insert " not match
if the two match but the amounts in columns C and G(????????) I want to give Ref number and state that the "refs match" .
if they refs in column B and F and amounts match in columns C and G I want to write the reference only.unquote


The second and third conditions are not clear. I am rephrasing themn


if column B and F match but the columns C and G DO NOT MATCH then in col D write
value in column B and add a string "refs match"


if column B and F match and colcumns C and G also match then in col D write value of column B


x is enough y is not necessary if you are doing match in EACH row .if each row is checked with all the rows revert back to newsgroup. that is a different problem

try this macro "test" and if necessary modify. before testings copy the data somewhere safely so that original data can be retrieved.

in code dim y is not necessary

Sub bankreconciliation()
Dim x, y As Integer


For x = 2 To Cells(Rows.Count, 1).End(xlUp).Row
'For y = 3 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(x, 2) <> Cells(x, 6) Then Cells(x, 4) = "no match"
'ElseIf Cells(x, 2) = Cells(y, 6) Then
'Cells(x, 4) = Cells(y, 6) & " REFS MATCH"
If Cells(x, 2) = Cells(x, 6) Then
If Cells(x, 3) <> Cells(x, 7) Then
Cells(x, 4) = Cells(x, "B") & " " & "REFS MATCH"
Else
Cells(x, 4) = Cells(x, "B")
End If
End If
Next x
End Sub

 

[PAULGOLD]

hi venkat
i tried the code and it is not quite what i was trying to say.
case 1
if the references in column B and F do not match then I want to put in column D " no match"
case 2
if the references in column B and F match but their corresponding amounts do not match I want to state " reference match but not amounts, "and give the reference.
case 3

if the references in column B and F match and corresponding amount match then I want to state " references and amounts match)
I want the vba to loop through the references in column B and F. it is not necessary and not expected that reference eg 692201 be on the same line but if they match wherever they are in column B and F I want the code to state theat given the three conditions above.
thanks for any help in rectifying my code.
Paul

 

[Peter_SSs]

Do you really need a macro?
Assuming that a cheque number will not occur more than once in either column B or column F, then does this formula, copied down, do what you want?

Excel Workbook
	A	B	C	D	E	F	G
1	cheque book				Bank Data
2	Date	cheque no	amount	matching cheque in bank	date	cheque no	amount
3	1/10/09	692201	40000	match	1/10/09	692203	23000
4	2/10/09	692202	50000	match	5/10/09	692201	40000
5	3/10/09	692203	23000	match	6/10/09	692205	51000
6	4/10/09	692204	29000	match	9/10/09	692202	50000
7	5/10/09	692205	51000	match	8/10/09	692204	29000
8	6/10/09	692206	35000	match	11/10/09	692206	35000
9	7/10/09	692207	15000	match	8/10/09	692208	1
10	8/10/09	692208	20010	reference match but not amounts 692208
11	9/10/09	692209	5130	no match	7/10/09	692207	15000
12	10/10/09	692210	12	no match	13/10/09	692213	1500
13	11/10/09	692211	1500	no match
14	12/10/09	692212	1500	no match
15	13/10/09	692213	1500	match
16
Match Cheques

If you do need a macro, then perhaps try this adaptation.

Sub Check_Matches()
  Dim lr As Long
  
  Const f As String = "=IF(COUNTIFS(F$3:F$#,B3,G$3:G$#,C3)>0,""match"",IF(COUNTIF(F$3:F$#,B3)>0,""reference match but not amounts "" & B3,""no match""))"
  
  lr = Range("F" & Rows.Count).End(xlUp).Row
  With Range("D3:D" & Range("C" & Rows.Count).End(xlUp).Row)
    .Formula = Replace(f, "#", lr, 1, -1, 1)
    .Value = .Value
  End With
End Sub