Ask a question »

Excel - A macro to check for errors

May 2015


Excel - A macro to check for errors




Issue


I have a report that I am using for a customer of mine that lists there kits in a matrix for me. Once in a while I find an error where a kit might have different items associated to it, which can't happen. So I am trying to create a macro of some sort to do some checks to make sure everything is populated correctly. Here is an example of the spreadsheet...

A B C D E
1Kit Item Item2 Item3 Results
2AAA ABC BCD CDE
3AAA ABC BCD CDE GOOD
4BBB BCD CDE DEF
5BBB ABC BCD CDE BAD
6BBB ABC BCD CDE GOOD
7CCC BCD CDE DEF
8CCC ABC BCD CDE BAD
9CCC CDE CDE DEF BAD
10CCC ABC BCD CDE


I need to write a macro that will do the following.
  • If cell A1 = A2 then check if B1 = B2 & C1 = C2 & D1 = D2.
  • If all match then post "GOOD" to E1, else post "BAD to E1
  • But if A1 <> A2 then leave E1 blank and step down to A2 vs A3
  • In the example above A2 = A3 and B2 = B3 & C2 = C3 & D2 = D3, so E3 = GOOD
  • In the example above A4 = A5 and B4 <> B5 & C4 <> C5 & D4 <> D5, so E5 = BAD

Solution


If you need a macro, here is the code:

Sub findResult() 
Dim Kit, Item, Item2, Item3, Results As Range 
Dim itemCount As Long 
itemCount = Range("A1").CurrentRegion.Rows.Count 
Set Kit = Range("A:A") 
Set Item = Range("B:B") 
Set Item2 = Range("C:C") 
Set Item3 = Range("D:D") 
Set Results = Range("E:E") 
For i = 3 To itemCount 
Select Case Kit(i, 1) = Kit(i - 1, 1) 
Case True 
If Item(i, 1) = Item(i - 1, 1) And Item2(i, 1) = Item2(i - 1, 1) _ 
And Item3(i, 1) = Item3(i - 1, 1) Then 
Results(i, 1) = "GOOD" 
End If 
If (Item(i, 1) = Item(i - 1, 1) And Item2(i, 1) = Item2(i - 1, 1) _ 
And Item3(i, 1) = Item3(i - 1, 1)) = False Then 
Results(i, 1) = "BAD" 
End If 
Case False 
Results(i, 1) = "" 
End Select 
Next i 
End Sub 



Thanks to Mehedad for this tip.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-a-macro-to-check-for-errors.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Excel - A macro to check for errors » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.