VBA to check multiple sheets to see if same items exist in a column.

[kelly mort]

Hello,
I need a way to check if same items appear in a column for multiple sheets.

I have a 5 sheets and I want to check if a column say Y, has the same items.
Let say if *mangoes" appear in that column on all sheets, there is a match else there is no match.

I came across this code written by @Joe4
The only issue I have with this is that it only checks for a single sheet.

Looking for VBA to check if all data in a column is the same (text)

Looking for VBA to check if all data in a column is the same (text) - For example - if I did it manually -top row cell 1 would contain" '=IF(AND(B1=B2,B2=B3,B3=B4,B4=B5,B5=B6,B6=B7,B7=B8,B8=B9),TRUE,FALSE) But ideally I need this to just loop thru and check that all cols contain same text -...

www.mrexcel.com

Sub Check()

    Dim myRange As Range
    Dim myValue
    Dim allSame As Boolean
    
'   Set column to check
    Set myRange = Range("C:C")
    
'   Get first value from myRange
    myValue = myRange(1, 1).Value
    
    allSame = (WorksheetFunction.CountA(myRange) = WorksheetFunction.CountIf(myRange, myValue))
    
'   Return whether or not they are all the same (TRUE/FALSE)
    MsgBox allSame
    
End Sub

I am thing of looping the sheets but with my current state of thinking, I will be resetting the Boolean each time which will trick me into thinking I am doing the right thing.

How do I get it done properly?

 

[Zot]

1) You only have one myValue to check in the sheets or you are trying to take each value in column C of Sheet1.
2) Do you want to count how many found or just want to know if there is recurrence only.

This will help others on best approach.

 

[Micron]

maybe

    Dim myRange As Range
    Dim ws As Worksheet
    Dim myValue
    Dim allSame As Boolean
 
'   Set column to check
    Set myRange = Range("C:C")
    For Each ws In Worksheets
'      Get first value from myRange
       myValue = myRange(1, 1).Value

       allSame = (WorksheetFunction.CountA(myRange) = WorksheetFunction.CountIf(myRange, myValue))
    Next

'   Return whether or not they are all the same (TRUE/FALSE)
    MsgBox allSame

If the formula doesn't return True (equals) on any sheet then the boolean will be false, which is how I interpreted the desired outcome.

 

[bebo021999]

Try:

Option Explicit
Sub match()
Dim myValue, f
Dim sht As Worksheet
Dim count&
myValue = Sheets("Sheet1").Range("A1").Value
    For Each sht In Sheets
        Set f = sht.Range("C:C").Find(what:=myValue, Lookat:=xlPart)
        If Not f Is Nothing Then
            count = count + 1
        Else
            MsgBox sht.Name & " does not match"
            Exit Sub
        End If
    Next
        If count = Sheets.count Then
            MsgBox "allSame"
        Else
            MsgBox sht(Sheets.count).Name & " does not match"
        End If
End Sub

 

[kelly mort]

1) You only have one myValue to check in the sheets or you are trying to take each value in column C of Sheet1.
2) Do you want to count how many found or just want to know if there is recurrence only.

This will help others on best approach.

I just want to know if the items are different.
For example, I want to have only mangoes in the column.
So if only mangoes appear in the column for all the sheets, there is a match.
But if orange should appear even once, there is no match.

That is for all the sheets, only one single word or category should be available in the column.

 

[kelly mort]

@bebo021999
Your code is promising but has a few issues:
1. The sht was called outside the loop so I updated it.
2. The sheets.count makes it return false always since there are more sheets but I am only checking 5 - using a case state.

  If count = 5 Then
            MsgBox "allSame"
        Else
            MsgBox  " does not match"
        End If

The above gave me the match alert when all items were same but when I changed one value on a sheet, expecting the does not match alert, it still gave the allsame alert.

A read the code again and saw that it was coming from this line:

Set f = sht.Range("C:C").Find(what:=myValue, Lookat:=xlPart)

There may be orange in the column but it will still return allsame because it has found mangoes in the search.

 

[kelly mort]

@Micron
I want the formula to return true in ALL sheets NOT ANY sheet.
I get false always even if there are oranges and mangoes in the columns (sheets).

 

[kelly mort]

Taking inspiration from the code of @Micron and @bebo021999 this is how I managed to get it working:

[CODE]
Sub myTestCheck()
    Dim ws As Object, myValue$, SameOnSheet As Boolean, lr&, eC As Range
    Dim mRng As Range, vFound As Boolean, dbCount&, mCount&
    dbCount = 0
    mCount = 0
    For Each ws In Worksheets
        Select Case ws.Name
            Case "SH1", "SH2", "SH3", "SH4", "SH5"
                vFound = False
                lr = ws.Cells(Rows.count, "B").End(xlUp).Row
                If lr > 6 Then
                    Set mRng = ws.Range("Y7:Y" & lr)
                    For Each eC In mRng
                        If eC <> "" Then
                            myValue = eC
                            vFound = True
                            Exit For
                        End If
                    Next eC
                End If
            Case Else
        End Select
        If vFound Then Exit For
    Next ws
    
    If vFound Then
        For Each ws In Worksheets
            Select Case ws.Name
                Case "SH1", "SH2", "SH3", "SH4", "SH5"
                    lr = ws.Cells(Rows.count, "B").End(xlUp).Row
                    If lr > 6 Then
                        dbCount = dbCount + 1
                        Set mRng = ws.Range("Y7:Y" & lr)
                        With WorksheetFunction
                            SameOnSheet = (.CountA(mRng) = .CountIf(mRng, myValue))
                        End With
                        If allSame Then mCount = mCount + 1
                    End If
                Case Else
            End Select
        Next ws
    End If
    
    If mCount = dbCount Then
        MsgBox "They are the same"
    Else
        MsgBox "They are not same"
    End If
End Sub

[/code]

 

[Micron]

Glad I was able to help, even if only a little bit. My Excel vba pales in comparison to my Access vba so at this point, a lot of the Excel properties and methods are like Greek to me.