Error: my code searches only within one cell

[anxoperez]

I'm trying to run the Find-dialogue upon Workbook open and I'm using this code. The problem is it performs the search only within whatever the active cell is, yet I want it to search forward (i.e., look for the next occurrence).

Can someone tell me how to do it?

Private Sub Workbook_Open()
'
' Macro2 Macro
'
Sheets("Memory").Select
Application.Dialogs(xlDialogFormulaFind).Show "", 2
End Sub

(I also want it to search for values and not formulas)

 

[Michael M]

Maybe change this line

Sheets("Memory").Select
to
Sheets("Memory").Cells.Select
or for eg
Sheets("Memory").Range("A1:D100").Select

 

[anxoperez]

That works and I had done something similar before. However I need to avoid Selecting any range, and this is why I wanted it to just look for the next occurrence within the sheet. The reason I don't want it to highlight a selection is because the many cells are color coded and when the findbox highlights the search result cell, I can't see it.

 

[anxoperez]

this issue is still unsolved and I need an answer. Would someone have a solution?

thanks

 

[Richard Schollar]

So you want it to find the next occurrence rather than the occurrence that may appear in the activecell? You could just offset the acivecell eg:

Sheets("Memory").Select
Activecell.Offset(,1).Activate
Application.Dialogs(xlDialogFormulaFind).Show "", 2

Or did you mean the find dialog literally only searches the activecell? Wat version of Excel are you using? How many cells are selected at the moment the sheet is activated? if only a single cell, the dialog should search all cells on the sheet

 

[anxoperez]

For some reason it doesn't work.

It still is searching only within the active cell and nothing beyond it.

I'll answer your questions:

- I want it to search in the whole sheet (so active cell AND the rest)
- But I don't want to specify an area because that highlights the searchable area and then I have a hard time spotting what if finds as the cells are color-coded.
- I'm using Excel 2011 for Mac
- Yes, I only have one cell highlighted when the doc is opened, but the core of the problem is that it only searches within a cell and not the entire sheet (I know this because the "no results" popup message only appears if I type something not contained within that cell).

 

[anxoperez]

I can't figure out what the problem is. It's really weird because if I close the search dialogue and open it again it DOES search the whole document, but if I don't, it only searches within that one cell.

I'm going crazy I hope one of you geniuses out there can provide the solution.