FIND a value using VBA

[PATSYS]

Hi all,

I want to find a value within the workbook.

Manually I would press CTRL + F, type the text in the FIND WHAT field, then set the WITHIN field to WORKBOOK.

When recoded this thru macro and tried to run it again, I got a debug error. See my code below

Sub Macro5()
    Sheets("Main").Select
    Cells.Find(What:="PATSYS", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
End Sub

I noticed that the Sheet is set when the macro is recorded and that if this sheet is not the active one when I run this code, I get the debug error.

How do I fix this?

Thanks in advance

 

[Marcelo Branco]

Hi PATSYS,

I could not replicate your problem. The code worked perfect for me being or not Main the active sheet.

M.

 

[PATSYS]

Hi Marcelo,

I need to correct my earlier observation.

I got the debug error when the value I am looking ("PATSYS") is not in the sheet "Main".

Do you get this same error?

Thanks

 

[Marcelo Branco]

Hi Marcelo,

I need to correct my earlier observation.

I got the debug error when the value I am looking ("PATSYS") is not in the sheet "Main".

Do you get this same error?

Thanks

Ok, i understood

Try this

Sub Macro5()
    Sheets("Main").Select
    On Error Resume Next
    Cells.Find(What:="PATSYS", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    If Err <> 0 Then MsgBox "PATSYS was not found in Sheet Main"
    On Error GoTo 0
End Sub

HTH

M.

 

[PATSYS]

Hi,

That will not look for the value in the entire workbook, will it? That will just give me me a message that it acn not find it in the sheet Main.

The code I need is one that will search the entire workbook (not just in the active sheet).

Basically I just want the way it is done in CTRL+F, only thru VBA.

 

[Marcelo Branco]

To search all the sheets try this

Sub Macro5()
    Dim wk As Worksheet
    
    For Each wk In ThisWorkbook.Worksheets
        wk.Select
        On Error Resume Next
        Cells.Find(What:="PATSYS", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        If Err = 0 Then MsgBox "PATSYS found in " & wk.Name: Exit Sub
    Next wk
    
    If Err <> 0 Then MsgBox "PATSYS was not found in any sheet"
End Sub

M.

 

[Marcelo Branco]

A better version (get multiple ocurrences in different sheets)

Sub Macro5()
    Dim wk As Worksheet, NotFound As Boolean
    
    NotFound = True
    For Each wk In ThisWorkbook.Worksheets
        wk.Select
        On Error Resume Next
        Cells.Find(What:="PATSYS", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        If Err = 0 Then MsgBox "PATSYS found in " & wk.Name: NotFound = False
    Next wk
    
    If NotFound Then MsgBox "PATSYS was not found in any sheet"
End Sub

M.

 

[PATSYS]

Hi Marcelo,

It works, thanks.

Is it possible to make the searched text variable e.g. whatever value is in worksheet MAIN, cell B20?

 

[Marcelo Branco]

Hi Marcelo,

It works, thanks.

Is it possible to make the searched text variable e.g. whatever value is in worksheet MAIN, cell B20?

I think so, if you use, in the FIND method, something like (NOT TESTED)

What:=Sheets("Main").Range("B20").Value

M.

 

[PATSYS]

I think so, if you use, in the FIND method, something like (NOT TESTED)

What:=Sheets("Main").Range("B20").Value

M.

Hi Marcelo,

It is almost perfect - the only thing is that it does not select the cell containing the searched value. It only activates the sheet where the value is located.

Can you further assist?

Thanks