VBA Macro HelpSolved/Closed

Question

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. Configuration: Windows 7 / Safari 535.19

Mehedad · Answer

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.

Mehedad · Answer

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! :)

froggy7897 · Answer

Thank you, this works perfect.

VBA Macro Help

3 responses

Similar discussions

Subscribe To Our Newsletter!