VBA Macro Help

Solved/Closed
froggy7897 Posts 2 Registration date Tuesday May 22, 2012 Status Member Last seen May 24, 2012 - May 22, 2012 at 03:27 PM
froggy7897 Posts 2 Registration date Tuesday May 22, 2012 Status Member Last seen May 24, 2012 - May 24, 2012 at 10:22 AM
Hello,

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

Hopefully this make sense and this is something that can be done. Any help would be greatly appreciated.

3 replies

Mehedad Posts 22 Registration date Thursday April 19, 2012 Status Member Last seen April 16, 2013 3
May 23, 2012 at 12:00 AM
I don't think Macro is needed, you can write the formula below in E2:

=IF(AND(A2=A1,AND(B2=B1,C2=C1,D2=D1)),"GOOD",IF(A2<>A1,"","BAD"))

Copy and Paste till the end.
0
Mehedad Posts 22 Registration date Thursday April 19, 2012 Status Member Last seen April 16, 2013 3
May 23, 2012 at 12:08 AM
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

Codes can be improved, I am not so efficient with the codes, but it still works! :)
0
froggy7897 Posts 2 Registration date Tuesday May 22, 2012 Status Member Last seen May 24, 2012
May 24, 2012 at 10:22 AM
Thank you, this works perfect.
0