Macro : Find Data Number in Only Cell Certainty in Multiple Sheets

[muhammad susanto]

hi all...

how to modify this code :
i want spesicifik find in only cell AD, row 14

Sub SearchAllSheets()    Dim ws As Worksheet
    Dim rFound As Range
    Dim strName As String
     
    On Error Resume Next
    strName = InputBox("What Name?")
    If strName = "" Then Exit Sub
    For Each ws In Worksheets
        With ws.UsedRange
            Set rFound = .Find(What:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
            If Not rFound Is Nothing Then
                Application.Goto rFound, True
                Exit Sub
            End If
        End With
    Next ws
    On Error GoTo 0
     
     MsgBox "Value not found"
End Sub

thanks in advance...

m.susanto

 

[mrshl9898]

Like this?

Sub SearchAllSheets()

    Dim ws As Worksheet
    Dim rFound As Range
    Dim strName As String
     
    On Error Resume Next
    strName = InputBox("What Name?")
    If strName = "" Then Exit Sub
    For Each ws In Worksheets
        With ws.Range("AD14")
            Set rFound = .Find(What:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart)
            If Not rFound Is Nothing Then
                Application.Goto rFound, True
                Exit Sub
            End If
        End With
    Next ws
    On Error GoTo 0
     
     MsgBox "Value not found"
End Sub

 

[muhammad susanto]

hello mrshl9898..

worked it ...

how to adding parameter if i found the same data/number in different sheets..the macro show msgbox "Find Next..., Find Next."

thanks again....

 

[mrshl9898]

Not a problem, is this what you were after?

Sub SearchAllSheets()

    Dim ws As Worksheet
    Dim rFound As Range
    Dim strName As String
     
    On Error Resume Next
    strName = InputBox("What Name?")
    If strName = "" Then Exit Sub
    For Each ws In Worksheets
        With ws.Range("AD14")
            Set rFound = .Find(What:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart)
            If Not rFound Is Nothing Then
                Application.Goto rFound, True
                MsgBox "Value found, find next"
            Else
                MsgBox "Value not found, checking next sheet"
            End If
        End With
    Next ws
    On Error GoTo 0
     
     MsgBox "Complete"
End Sub

 

[muhammad susanto]

great!!..

how to skip "value not found..."i mean only founded...
like msgbox "value found, checking next sheet" cause in my sheets contains duplicate number in range "AD14"
many thanks

 

[mrshl9898]

You can just delete the line. and the "Else" above it

 

[mrshl9898]

Do you want to know if nothing is found though?

Sub SearchAllSheets()

    Dim ws As Worksheet
    Dim rFound As Range
    Dim strName As String
    Dim valfound As String
     
     
    valfound = 0
    On Error Resume Next
    strName = InputBox("What Name?")
    If strName = "" Then Exit Sub
    For Each ws In Worksheets
        With ws.Range("AD14")
            Set rFound = .Find(What:=strName, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart)
            If Not rFound Is Nothing Then
                Application.Goto rFound, True
                MsgBox "Value found, find next"
                valfound = 1
            End If
        End With
    Next ws
    On Error GoTo 0
     
     If valfound = 0 Then
     MsgBox "Value not found"
     End If
     
End Sub

 

[muhammad susanto]

still not fully work..i mean for find number = 2, showing 2,23,32,42, etc,,,,,,
i want exactly number...

 

[mrshl9898]

Try this:

Sub SearchAllSheets()

    Dim ws As Worksheet
    Dim rFound As Range
    Dim strName As String
    Dim valfound As String
     
     
    valfound = 0
    On Error Resume Next
    strName = InputBox("What Name?")
    If strName = "" Then Exit Sub
    For Each ws In Worksheets
        If ws.Range("AD14").Value = strName Then
                ws.Activate
                Range("AD14").Select
                MsgBox "Value found, find next"
                valfound = 1
        End If
    Next ws
    On Error GoTo 0
     
     If valfound = 0 Then
     MsgBox "Value not found"
     End If
     
End Sub

 

[muhammad susanto]

still not working...