0
Thanks

A few words of thanks would be greatly appreciated.

# VBA - A macro to check data

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

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.
0
Thanks

A few words of thanks would be greatly appreciated.

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

## Related

This document, titled « VBA - A macro to check data », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Recommended

DON'T MISS

TRENDING GAMES & APPS
• Professional

• Internet

• Internet

• Professional

• Internet

• Internet

• Video games

• Internet

• Video games

• Professional

• Video games