Find By Column - fails

slack7639

Board Regular
Joined
Apr 19, 2016
Messages
53
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,479
Office Version
  1. 365
Platform
  1. Windows
In way will it only find by rows?
 

slack7639

Board Regular
Joined
Apr 19, 2016
Messages
53
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:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Code:
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
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,479
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 19, 2016
Messages
53
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,479
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,721
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top