Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and matchcase (Excel 2003 SP1)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and matchcase (Excel 2003 SP1)

    Can you post a sample workbook?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and matchcase (Excel 2003 SP1)

    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
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and matchcase (Excel 2003 SP1)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and matchcase (Excel 2003 SP1)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and matchcase (Excel 2003 SP1)

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

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Find and matchcase (Excel 2003 SP1)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and matchcase (Excel 2003 SP1)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •