CODEWITHSUNDEEP
vbaexcel
user3545063
user3545063

Reputation: 811

Check if cell value appears in any work sheet

As the title states, I want to check inside of a function whether a value is already inside of the workbook somewhere. My current thought process was to loop through all of the worksheets while checking if the value I'm looking for appears in any cell of the range.

For some reason, this code actually does lookup duplicate values, but only inside of 1 work sheet (it ignores the other sheets).

Public Function foundDuplicateID(cellValue As Double) As Boolean
Dim Rng As Range
Dim cell As Range
Dim duplicatesFound As Integer

duplicatesFound = 1 ' Initializing as 1 since the first "duplicate" is the number that is searched for itself

' Declare Current as a worksheet object variable.
Dim Current As Worksheet

Application.ScreenUpdating = False

' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
    Current.Activate

    ' Value Range (Same range for all worksheets)
    Set Rng = Range(Range("B7"), Range("B" & Rows.Count).End(xlUp)

    If Not Rng.Find(cellValue, , Excel.xlValues) Is Nothing Then
    duplicateFounds = duplicateFounds + 1
    End If

Next

If duplicatesFound = 1 Then
    foundDuplicateID = False

ElseIf duplicatesFound > 1 Then
    foundDuplicateID = True
End If

End Function

Upvotes: 0

Views: 112

Answers (1)

DisplayName
DisplayName

Reputation: 13386

you most probably are using that Function as a UDF inside a cell. But UDF cannot change interact with UI so they can't activate a sheet different from the currently active one.

change your loop code to reference Current sheet, as follows:

' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
    With Current ' reference current sheet
        ' Value Range (Same range for all worksheets)
        Set Rng = .Range(.Range("B7"), .Range("B" & Rows.Count).End(xlUp)) ' each referenced sheet members are a simple dot away from it
        If Not Rng.Find(cellValue, , Excel.xlValues) Is Nothing Then duplicateFounds = duplicateFounds + 1
    End With
Next

moreover you have a typo in some duplicatesFoundvariable referncing: use Option Explicit to catch them (and have other benefits)

Option Explicit

Public Function foundDuplicateID(cellValue As Double) As Boolean
    Dim Rng As Range
    Dim duplicatesFound As Integer

    duplicatesFound = 1 ' Initializing as 1 since the first "duplicate" is the number that is searched for itself

    ' Declare Current as a worksheet object variable.
    Dim Current As Worksheet

    Application.ScreenUpdating = False

    ' Loop through all of the worksheets in the active workbook.
    For Each Current In Worksheets
        With Current
        ' Value Range (Same range for all worksheets)
            Set Rng = .Range(.Range("B7"), .Range("B" & Rows.Count).End(xlUp))
        End With
        If Not Rng.Find(cellValue, , Excel.xlValues) Is Nothing Then duplicatesFound = duplicatesFound + 1

    Next

    foundDuplicateID = duplicatesFound > 1

End Function

a further refactoring of your code would lead to:

Option Explicit

Public Function foundDuplicateID(cellValue As Double) As Boolean
    Dim duplicatesFound As Long ' better use Long instead of Integer and avoid overflow issues

    Dim Current As Worksheet

    Application.ScreenUpdating = False

    ' Loop through all of the worksheets in the active workbook.
    For Each Current In Worksheets
        With Current ' reference current sheet
            If Not .Range(.Range("B7"), .Range("B" & Rows.Count).End(xlUp)).Find(cellValue, , Excel.xlValues) Is Nothing Then duplicatesFound = duplicatesFound + 1
        End With
    Next
    foundDuplicateID = duplicatesFound > 0

    Application.ScreenUpdating = True
End Function

Upvotes: 1

Related Questions