Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Search or Find String Value

    I found this code and tested a few others, but this seems to work for what's required.
    The problem is the recorded macro to Find something.
    In some scenarios I use this astras * as a marker then run a macro to Find it, then proceed with
    Selection.Offset(0, 1).Select or whatever the case may be because the marker may land anywhere in a Sheet Cell Range

    The question/s is,
    Does VBA sometimes does and other times does not recognize the astras * as a string Value ?
    Because when on test I tried, FindString = ("*") it won't Find it. Other times in other projects it has worked OK.
    Yet when on test I use, FindString = ("XYZ1") it does Find it, always.
    Preferably I would like to use the astras * 100% of the time, because it's original,unique and "signature".
    The Code so far is:
    Code:
    Sub Find_Somthing()
        Dim FindString As String
        Dim Rng As Range
    
        FindString = ("*")' does not work 100% of the time.
        FindString = ("XYZ1")' works 100% of the time.
    
        If Trim(FindString) <> "" Then
            With Sheets("SEQNCE").Range("A1:Z100")'optional range/s
                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                Else
                    MsgBox "Nothing found"  ' optional
                End If
            End With
        End If
    End Sub
    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    An asterisk in find is a wildcard (for mulitple characters). To search a literal asterisk, use
    FindString = "~*"
    [Note the parentheses are not required to assign a string variable]

    Similarly to find a question mark (wildcard for a single character) use:
    FindString = "~?"

    And since the tilde is used for "non-wildcard" indicator, to find the literal tilde use:
    FindString = "~~"

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    RetiredGeek (2013-12-11)

  4. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks

    SOLVED.
    Code:
    To search a literal asterisk, use
    FindString = "~*"

Posting Permissions

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