• Find and matchcase (Excel 2003 SP1)

    Author
    Topic
    #440811

    I have some code which searches through all the sheets in a workbook to find a specified string :

    Set rngFound = rngSearch.Find(what:=strSearchText, LookIn:=xlValues, MatchCase:=False)

    When it searches for ‘GBP’ it finds 6 references. However, when it searches for ‘gbp’ it finds none. I cannot understand why this should be so, as when I use Edit/Find & ‘gbp’ manually it finds 6 references.

    Can anyone work out why this should be so

    Usual thanks for your expert help

    Nick

    Viewing 1 reply thread
    Author
    Replies
    • #1056870

      Can you post a sample workbook?

      • #1056872

        Hans

        The search code is usually in a separate workbook but I have added the module to the samplefind.xls. The “GBP” to find is in column W.

        Thanks

        Nick

        • #1056878

          Could you post a simplified version of the code? I don’t want to wade through hundreds of lines of code to find out which parts I need.

          • #1056880

            Hans

            This is the main part of the code :

            For Each sht In wkb.Worksheets
            Application.StatusBar = “Processing Workbook : ” & wkb.Name & ” Worksheet : ” & sht.Name
            Set rngLastCell = LastCell(sht)
            If Not (rngLastCell Is Nothing) Then
            With sht

            ‘———————————————————–
            ‘Find all references to search text
            ‘THIS IS WHERE MATCH CASE DOES NOT SEEM TO WORK
            ‘———————————————————–
            Set rngSearch = .Range(.Cells(1, 1), .Cells(rngLastCell.Row, rngLastCell.Column))
            If blnSearchValues Then
            Set rngFound = rngSearch.Find(what:=strSearchText, LookIn:=xlValues, MatchCase:=blnMatchCase)
            Else
            Set rngFound = rngSearch.Find(what:=strSearchText, LookIn:=xlFormulas, MatchCase:=blnMatchCase)
            End If
            If rngFound Is Nothing Then
            ‘do nothing
            Else
            ‘Code to store references deleted here
            End If
            End If
            End With
            End If
            Next

            • #1056882

              If you would like us to help you, please help us by providing something we can use.

    • #1056888

      There is nothing syntactically wrong with your code so I can only guess that the range you are searching may not be what you think it is, or the boolean for matchcase is not the value you think.

      • #1056901

        My fault – I was looking in the wrong place in the code.

        I had coded : InStr(1, rngFound.Formula, strSearchText) & it should have been : InStr(1, rngFound.Formula, strSearchText, vbTextCompare)

        It now works properly.

        Thanks for your help

        Nick

    Viewing 1 reply thread
    Reply To: Find and matchcase (Excel 2003 SP1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: