Question on the Excel Search Feature

[MPW]

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.

 

[Yongle]

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

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[MPW]

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.

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]

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?)

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]

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.

 

[Yongle]

It appears that you have bucked the natural order of things
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]

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!

 

[jolivanes]

Re: "Free Audio Bible - Faith Comes By Hearing Audio Bible - Hear the Audio Bible - Read the Bible"
What does this have about Excel? After all, it is an Excel Forum.

 

[xenou]

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]

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.