Less Specific Alphabetical Search Option

[torweb]

Is it possible to modify this code to peform a more gereral search...by first letter only.

Sub digitup()
Dim x As Variant
Set x = Cells.Find(What:=InputBox("Please enter your search criteria", "Search"), LookIn:=xlFormulas, LookAt:=xlPart)
If Not x Is Nothing Then
x.Activate
Else
MsgBox "Not found."
End If

End Sub

Thanks in advance!!!

 

[Damon Ostrander]

The following variation to your code using the Left() function should limit the search to the first character only.

Sub digitup()
Dim x As Variant
Set x = Cells.Find(What:=Left(InputBox("Please enter your search criteria", "Search"),1), LookIn:=xlFormulas, LookAt:=xlPart)
If Not x Is Nothing Then
x.Activate
Else
MsgBox "Not found."
End If

End Sub

 

[Damon Ostrander]

Oops. Never mind. The method I proposed will look for the first letter anywhere in the cell.



Damon

 

[Damon Ostrander]

Okay, try this:

Sub digitup()
Dim x As Range
Dim Search As String
Search = InputBox("Please enter your search criteria", "Search")
For Each x In ActiveSheet.UsedRange
If x.Value Like Left(Search, 1) & "*" Then
x.Activate
GoTo Found
End If
Next x
MsgBox "Not found."
Exit Sub
Found:
End Sub

This doesn't use the Find method, but uses a loop instead. It only tries to match the first letter of the cell contents. I assumed you didn't really mean to search the formula as indicated in your previous post, since I believe this would always yield an equal sign, but rather the formula evaluation results.

 

[TryingToLearn]

Try this:

Sub digitup()
    Dim x As Range
    Dim y, faddress As Variant
    With Sheets("sheet1").Cells
        y = InputBox("Please enter your search criteria")
        Set x = Cells.Find(What:=Left(y, 1), LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        If Not x Is Nothing Then
            faddress = x.Address
            If UCase(Left(x, 1)) <> UCase(Left(y, 1)) Then
                Do
                    Set x = .FindNext(x)
                Loop While Not x Is Nothing And x.Address <> faddress
            End If
            x.Activate
        Else
            MsgBox "Not found."
        End If
    End With
End Sub

EDIT: Damon is a faster coder than me! My understanding is the find is faster than the loop through each cell though. Guess the only thing that matters is it does what you want it to.

HTH

 

[torweb]

Thanks both for the reply and ideas. Unfortunately, they both ran very slow and didn't seem to locate my search criteria (the letter B or A, or etc.). Also, if I replaced the LookIn:=xlFormulas with LookIn:=xlValues, my original code also ran very slow (we're searching through 6,200 entries).

I copied both codes into my sheet, ran them, and waited until they returnd from their searches....that's what happened...

Thanks very much again for the input!!!

 

[Greg Truby]

Just thinking of the obvious... If you know the column, did you already try autofilter? Using the Custom option you can pick from Begins With, Ends With, Contains, Does not Contain and so forth. Only get two criteria though...