Find & Replace Dialog

[purceld2]

Hi

I do a lot finding strings that can be anywhere in the workbook (Ctrl-F) so I decided to automate it with a macro (see code below)

Its a be crude but it works using the value of the cell i have preselected. The problem I have is that when I run the macro it finds that value fine but there may be others so i just click Next.

The issue I am having is that the Within: Value is set to sheet and I need to have automatically set to Workbook or there is the possibility that I inadvertently miss some values.

Can someone show me how this can be done please.

Sub DesFIND()
'
' DeFIND Macro
'
' Keyboard Shortcut: Ctrl+m
'
Dim Cellvalue As String
    
    Cellvalue = ActiveCell.Value
    
    'Selection.Copy
    Cells.Find(What:=Cellvalue, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
        xlNext, MatchCase:=True, SearchFormat:=False).Activate

     Application.CommandBars("Edit").Controls("Find...").Execute
   

End Sub

 

[JLGWhiz]

You will need to loop through the sheets.

Dim sh As Worksheet
For Each sh in ThisWorkbook.Sheets
    'Your code to find here
Next

You will need to make some adjustments to your existing code so that it is tied to the sh variable.

 

[purceld2]

Thanks for your reply

I don't understand, if there is an options of Within: Sheet or Workbook in the Find Replace dialog box cannot it be set in VBA?

Why do you have to use a loop? Which does not give me the desired results as I need to examine each occurrence

 

[rlv01]

As purceld2 says, you can use the standard Find dialog to search the whole workbook

 

[Fluff]

I don't understand, if there is an options of Within: Sheet or Workbook in the Find Replace dialog box cannot it be set in VBA?

No it cannot be set in VBA. With VBA you need to loop through the sheets as mentioned by JLGWhiz.

 

[JLGWhiz]

Why do you have to use a loop? Which does not give me the desired results as I need to examine each occurrence

I don't know. I guess Bill Gates retired before he thought about including this in the VBA find function.
But on the serious side, there are many functions in Excel that are not directly incorporated into VBA parameters, and vice versa. They are separate applications, designed and maintained by different engineers and programmers. So one should not expect that because a function is available in one application that it should automatically be incorporated into the other. However, VBA can usually be applied so that it will do any of the functions that are done in Excel with execution time maybe being different.
If you think about it, the Find function in Exce is not the same as the Find dialog facility, nor the VBA Find function.