Question on the Excel Search Feature

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
532
Office Version
  1. 365
Platform
  1. Windows
I have noticed in the older versions of Excel, that the search seemed to fail if the text in the cells had too many keystrokes.
Was this true and if so, was it fixed somewhere along the line?

Many moons ago I began using loops to find/replace instead of using the built in feature. I would loop cell by cell and character by character. It was actually faster than it sounds and enabled me to really get specific with the find replace.

I have recently noticed that some of the machines using this process are freezing up. In addition, the resource meter pegs into the red. Even on faster machines. I do not seem to have any problems with my 365 office version, but Office 16 does have this problem. I have noticed that if the Excel window is minimized and then restored to the old size it will free it up sometimes..

Anyway I just thought I would see if anyone had a faster solution than what I have been using.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Anyway I just thought I would see if anyone had a faster solution than what I have been using.
what have you been using ?
where are you searching ?
what are you searching for ?
what is the expected output ?
XL2BB is helpful for posting sample data and results
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
532
Office Version
  1. 365
Platform
  1. Windows
Sorry for the delay Yongle.
All good questions:
what have you been using ? I take it that you would like to see some of the code? This is not the whole routine, but just a reduced version. If you would like I could sent the rest, but I figure that my problem would be found in the loops. One thing I did not include in my initial post is that this workbook is used with foreign fonts and languages. The main goal of this workbook is to format and align these languages to create a dramatized script that will be read by native speakers of that language. It is these strange fonts that could be at the heart of my problems.
VBA Code:
srch = Workshop.ComboBox25.Text
reft = Sheets("Main").Cells(Rows.Count, cla).End(xlUp).Row
If Workshop.CheckBox11.Value = True Then                          ‘starts at top of the worksheet or after current row
     strt = 2
     ElseIf Workshop.CheckBox11.Value = False Then
     strt = ActiveCell.Row + 1
End If
For r1 = strt + 1 To reft
     curcell = Sheets("Main").Cells(r1, cla)
     For y1 = 1 To Len(curcell) - (Len(srch) + 1)
          If Mid(curcell, y1, Len(srch)) = srch Then
               Sheets("Main").Cells(r1, cla).Select
               findfill                       ‘This populates another textbox using a select case routine. I did not think this was the problem since it only fires once.
               Application.ScreenUpdating = False
               cptr = Sheets("Main").Cells(ActiveCell.Row, 18)
               vsr = Sheets("Main").Cells(ActiveCell.Row, 19)
               Workshop.ComboBox29.Text = cptr
               If vsr = "<<" Then
                    Workshop.ComboBox30.Text = 1
               ElseIf vsr <> "<<" Then
                    Workshop.ComboBox30.Text = vsr
               End If
               Sheets("Main").Cells(r1, cla).Select
               Application.ScreenUpdating = True
               Workshop.Label112.Width = 0
               Exit Sub
          End If
     Next y1
Next r1
where are you searching ? I am searching in one column that varies in rows, usually averaging about 10,000.
what are you searching for ? Whatever the user types into Workshop.ComboBox25.Text. Most times it is only 1 keystroke, but could be a whole word or sentence.
what is the expected output ? The row that contains the info is selected and the routine exits after updating some other info on the userform.

As stated earlier, this seems to work well in Office 365, but hangs up in Office 2016.
Please let me know if you have other questions or if I did not provide enough info about what you did ask.
Thanks for taking the time to help.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
This code performs a case-insensitive search for text contained in variable srch
Values in column A in sheet "Main" are searched
Matching cells are captured in uRng
After all cells searched, message box with count of matching cells is returned
List of all found cells is then written to VBA immediate window
( view immediate window with CTRL g )

Q1 - are results as expected?
Q2 - do you need to find more than one incidence of srch in any one cell?
(if so, let me know and cells in uRng can be interrogated - do you require position in cell string for every one?)

VBA Code:
Sub FastSearch()
    Dim cell As Range, rngA As Range, uRng As Range, addr As String, srch As String, cla
    srch = Workshop.ComboBox25.Text
'range to search
    With Sheets("Main")
        Set rngA = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    End With
'find every cell containing srch
    Set cell = rngA.Find(srch)
    If cell Is Nothing Then
        Debug.Print srch & " Not found"
        Exit Sub
    End If
    addr = cell.Address
    Set uRng = cell
    Do
        Set cell = rngA.FindNext(cell)
        Set uRng = Union(uRng, cell)
    Loop While addr <> cell.Address
'use uRng
    MsgBox srch & " found in " & uRng.Cells.Count & " cells", , ""
    For Each cell In uRng
        Debug.Print cell.Address(0, 0)
    Next
End Sub
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
532
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you Yongle,
It turns out that my code did not need to change. When the endusers changed from Office 2016 to Office 365, the problem went away.

Thank you for all of the effort.
 
Solution

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
It appears that you have bucked the natural order of things :unsure:
Much of the moaning on here is about things deteriorating with the introduction of Exce 365 👿
With that kind of luck it could be time to buy a lottery ticket 😃
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
532
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

We have been using Excel as a Bible recording script in different languages since the turn of the century (makes it sound so old).
Over the years we have added thousands of lines of code to assist in the process. Everything from statistics, user displays to present the lines to read, time keeping, you name it. It has really developed a life of its own (pun intended) to the point where they now want to replace it with an actual program. MS has always been a challenge as they fix things. Many times we have had to plug in work arounds when they add a new feature.

This latest bout is documented as a known issue. Just found this link today.:
Excel 2016 crashes after editing cells

It was the real culprit of what I was fighting. Moving to 365 cured my ills. OBTW if you would like to see the results of a 20 year old bloated piece of workbook, checkout Free Audio Bible - Faith Comes By Hearing Audio Bible - Hear the Audio Bible - Read the Bible

Anyway with all that, thanks for the heart to assist when needed. I have worked through many issues on this site over the years. You guys rock!
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,797
Office Version
  1. 2019
Platform
  1. Windows
Seems there is an Excel connection...
We have been using Excel as a Bible recording script in different languages since the turn of the century (makes it sound so old).
Over the years we have added thousands of lines of code to assist in the process.

I have heard of people saying the traffic system planning of major cities or online version of large newspapers have been done in Excel ... is there anything you can't do in Excel?!
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
532
Office Version
  1. 365
Platform
  1. Windows
Thank you Xenou, you can do anything with Excel/VBA even down to the machine code level. The point still is that MrExcel has helped me many times to work past issues, I only wish I found the site sooner.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top