Excel VBA Test Three Cell Comparisons

[JustATheory]

Greetings,

I need to test for three cell comparisons before doing a particular calculation. If any of the three fail, then I need to move down a row and test again. I've tried If statements and ElseIf, but no sure if their is neater way.

A B C
Account DateFrom DateTo
123456 1/1/05 1/31/05
123456 1/1/05 1/31/05
123456 1/15/05 1/31/05
123456 1/15/05 2/14/05
789123 1/15/05 2/14/05
789123 1/15/05 2/14/05

In this example these are the pass / fail scenarios:

Rows 1 & 2 Pass
2 & 3 Fail
3 & 4 Fail
4 & 5 Fail
5 & 6 Pass

I appreciate any advice you can give.

Thanks,
Andy

 

[PHV]

I appreciate any advice you can give
On which code ?

You do something like this (r is the row number) ?
r = 2
While Cells(r, 1) <> ""
If Cells(r, 1) = Cells(r+1, 1) And Cells(r, 2) = Cells(r+1, 2) And Cells(r, 3) = Cells(r+1, 3) Then
' your Calculation here
End If
r = r + 1
WEnd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

I think PHV's example will return True for the first of each pair but not for the second. Extending it a slightly:

If (Cells(r, 1) = Cells(r + 1, 1) And _
    Cells(r, 2) = Cells(r + 1, 2) And _
    Cells(r, 3) = Cells(r + 1, 3)) _
    Or _
    (Cells(r, 1) = Cells(r - 1, 1) And _
    Cells(r, 2) = Cells(r - 1, 2) And _
    Cells(r, 3) = Cells(r - 1, 3)) _
Then

Without VBA you could do similarly with the And() and OR() functions in a helper column

You could consider concatenating the 3 columns in the worksheet and then testing to see if the concatenated result matches the previous or the next row.


Gavin