Find By Column - fails

[slack7639]

Using Excel 2010

Sometimes, when I have it set to Find By Columns, it will only Find By Rows. Any idea why?

Toggling the setting doesn't help.

Maybe closing and re-opening the xls? I don't think that worked.

 

[Fluff]

In way will it only find by rows?

 

[slack7639]

Ctrl+F
Options
Search . . . by Rows . . . or . . . By Columns
Find Next

Wait, you're making me think. Maybe this macro I run messes up sometimes, somehow . . . Apostrophes at the beginning are comments, or just things I was trying that didn't work . . . The VBA that runs is in red text:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Sub Find_By_Columns(control As IRibbonControl)

' Use this macro to reduce the number of clicks to bring up search "By Columns"

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
    
' Narrow the Formula Bar
[COLOR=#ff0000]     Application.FormulaBarHeight = 2[/COLOR]
' Go to the bottom right of the Worksheet
    
[COLOR=#ff0000]     Range("AC31").Select
    Selection.End(xlDown).Select
   
    ActiveWindow.SmallScroll up:=29
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone[/COLOR]

' It searches, starting from the top of the next column.
' If you want to search in column A, there would be no previous column, so, start in the bottom right of the Worksheet.

' You might want to start in a specific column, though, so select the previous column of where it makes sense to start.
' How do you do "Home", to get to the beginning of the row? . . . https://www.mrexcel.com/forum/excel-questions/415379-vba-equivalent-home-key.html
' Cells(Row, Column) . . . https://www.homeandlearn.org/the_cells_property.html
' 0 means "Scroll, False" . . . https://msdn.microsoft.com/en-us/library/office/aa195750(v=office.11).aspx
' If you don't want it to scroll, you could also use the word "False", or omit the value
' If you do want scroll, add the number 1, or "True"
    
'   Application.Goto Cells(ActiveCell.Row, 1), 0
'   Cells(ActiveCell.Row, "M").Activate

' NumLock gets turned off sometimes, and it's complex to code it to be on:
' https://stackoverflow.com/questions/25977933/sendkeys-is-messing-with-my-numlock-key-via-vba-code-in-access-form/39217833
' Simpler way:  If it's off, just click the Search button one or two more times to turn it on.
'   SendKeys "{NUMLOCK}", True

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

' First, run this "find", just to set the default parameters of the dialog:

        [COLOR=#ff0000]Cells.Find what:="", _
            After:=ActiveCell, _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False[/COLOR]
            
' LookIn:=xlFormulas, _
' Please explain difference between Find --> Look In --> Values vs Formulas
' https://www.mrexcel.com/forum/excel-questions/777308-please-explain-difference-between-find-look-values-vs-formulas.html

' Now, run the dialog:

[COLOR=#ff0000]         Application.CommandBars("Edit").Controls("Find...").Execute[/COLOR]

' This is a simpler dialog that can be used for "Find"
'       Application.Dialogs(xlDialogFormulaReplace).Show
' I changed "Replace" to "Find" - Previously, it then crashed Excel.  Now it doesn't?
'       Application.Dialogs(xlDialogFormulaFind).Show

' For some reason, bringing this up in a macro doesn't give you a blinking cursor in "Find what"
' You can just start typing, though, and it works like it normally does.
' Alternatively, use Sendkeys to activate it - The simplest way might be to send a "Del"
' https://msdn.microsoft.com/en-us/library/office/aa202943(v=office.10).aspx
' https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-sendkeys-method-excel

'        SendKeys ("{DEL}"), True
[COLOR=#ff0000]         Application.SendKeys ("{DEL}")[/COLOR]

' Alt+T could expand it, but the clicks and status make it inconsistent
' https://docs.microsoft.com/en-us/office/vba/api/excel.application.sendkeys

'       Application.SendKeys ("%t")

' Then, what if you want it to open, so you see all the "Options" every time?
' The macro does not bring this up "expanded".
' You have to click "Options", and then it stays that way, unless you close and re-open Excel.
' Well, see how they underscore a letter in some of the words?  That means to do . . . Alt + that letter . . . to activate it
' So, to see the "Options" every time, send Alt+t
' . . . There are some bugs with this, as described in the following:

'        SendKeys ("%t"), True
       
' I put the cursor in various places in the Worksheet, with just one "%t"
' Sometimes you have to click it twice for it to work.  What's that about?  [Alt+t is being sent again, which collapses it]
' I don't know, but instead of clicking it twice, I see that it works if I put in another "%t" in there, so I'll do that.
      
'       SendKeys ("%t%t"), True

' The next day, this doesn't work.

' Try two of Alt+t's, with a second in between.
' VBA pausing by tenths of seconds (or milliseconds)
' . . . I think we have established in prior threads that Wait and OnTime functions only have a "resolution" of one second.
' . . . For finer control, I think you'll need to use API calls
' . . . https://bytes.com/topic/visual-basic/answers/738464-vba-pausing-tenths-seconds-milliseconds

'       SendKeys ("%t"), True
'       Application.Wait (Now + TimeValue("00:00:01"))
'       SendKeys ("%t"), True

' Then, this didn't work . . . Is there some issue with Sendkeys not working?

' What if I put a wait of 1 second between the opening of "Find and Replace," and sending Alt+t?
' Fails.  When you click it again, it collapses "Find and Replace" . . . Wait, that's what it's doing.

' What's going on is, the first time you open it, Alt+t works to expand "Find and Replace"
' . . . But click the macro a second time, and Alt+t collapses "Find and Replace"
' So, Alt+t is good to get it to open the first time, but since the behavior of this dialog is to remain how you set it throughout your Excel session,
' . . . the next time you click it, the macro collapses it.
' So, you could have one button that opens "Find and Replace," and another (without Alt+t) for your next use of "Find and Replace",
' . . . or, simply open it *without* Alt+t, and manually click "Options" to see that if you want it, or leave it collapsed (functionality is there),
' . . . and it will be how you set it for your Excel session - THIS SEEMS EASIEST, DO THIS

' I'm not entirely sure on the "SendKeys" syntax.
' I think it's probably a good idea to have "True", so it waits.
' . . . True to have Microsoft Excel wait for the keys to be processed before returning control to the macro.
' . . . False (or omitted) to continue running the macro without waiting for the keys to be processed.
' It looks like you're supposed to write it this way, but the compiler rejects it:
' SendKeys ({DEL}, True)

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

' See the bottom of "Process_Bookmarks" for details.  Used at the end of macros, when necessary.

[COLOR=#ff0000]    SendKeys "{NUMLOCK}", True[/COLOR]
    
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

End Sub

 

[Fluff]

I've absolutely no idea what that code is doing.
Can you please explain in words why you think that Ctrl F is only searching by rows?

 

[slack7639]

It's a macro for one of my Ribbon buttons (Find by Columns) . . . it sets my default "Find" parameters (Find by Columns), and opens the dialog box

Sometimes, when I click "Find Next", it has switched to "Find by Rows" - this is what I'm trying to figure out - Why?

Sometimes, after I have run the macro, I close the dialog box, and put it at the bottom of a column, Ctrl+F, and expect it to "Find by Columns" in the next column - but it has switched to "Find by Rows"

I'm thinking that maybe I close and re-open it, and that changes the settings.

The next time it happens, I'll try and figure out what I did just before - of course, I can't get it to do this now.

 

[Fluff]

Xl remembers your settings for find/replace, so if it's changing either you changed it manually or you used a macro that changed the settings