Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I came across this VBA code (from https://www.wallstreetmojo.com/vba-find-function/#popmake-95356)
Sub FindIt ()
Dim FindThat As String
Dim Rng1 As Range
FindS = InputBox ("Enter the value you want to search")
With Sheets ("Sheet2").Range("A:A")
   Set Rng = .Find(What:=FindString, After:=Range("A2"))
   If Not Rng Is Nothing Then
                  Application.Goto Rng, True
              Else
                  MsgBox "Nothing Found"
              End If
              End With
End Sub


If I understand this, wouldn't my English language-algorithm of it below correctly describe the code above?
Begin Function
 Set Dim to FindThat As String
 Set Dim to Rng1 As Range
Have FindS as InputBox with Text ("Enter the value you want to search")
With Excel Sheet ("Sheet2") staring at Range("A:A")
     Set Rng on FindString looking after value in Range("A2")
     If Rng matches value then
                  Show match
              Else
                  Show MsgBox as "Nothing Found"
              End If loop
              End With loop
End Function


Was my interpretation of it correct?

If it is correct, then doesn't this code limit its search to one value in one column?
I want to change the code in a way that finding a value like " 4 1 2 4 0 2" and its variants can be found in a worksheet.
https://i345.photobucket.com/albums/p372/larawanflea/Untitled_zpsgpoc7yfo.jpg[^]

Hence...
1) Is it possible to search variants of a value per line across multiple columns?
2) What line needs to be changed in the code for that to happen?

What I have tried:

So far, I just studied the code and ran it. I tried looking up other sources that fit my concern but none showed up in my search results...
Posted
Updated 16-Jan-20 5:43am
v2
Comments
Richard MacCutchan 16-Jan-20 10:35am    
Modify the code to your requirements and run some test, fix cycles until it works.

1 solution

1. That code does indeed limit it's search to one value in one column.

2. The way you have interpreted the code is rather strange e.g.
Set Dim to FindThat As String
No - that line should be interpreted as
declare a variable called 'FindThat' which is of type 'String'
Your thoughts
Have FindS as InputBox with Text ("Enter the value you want to search")
should be
Display an Input box with the caption 'Enter the value you want to search' and assign the user input to the variable 'FindS'
Your interpretation of the with statement is also flawed
With Excel Sheet ("Sheet2") staring at Range("A:A")
should be
The following lines of code (up to the End With) refer to sheet "Sheet2", Column A
There is no concept of "starting at" here.
The phrase
Set Rng on FindString looking after value in Range("A2")
doesn't make sense. It should be something like
Search for the text in variable 'FindS' ignoring any cells before A2 and assign the result to variable 'Rng'
Even
If Rng matches value then
             Show match
should be
If there is a cell result in variable 'Rng' then position the cursor in that cell
You got the rest of it right.
3. You asked
Quote:
Is it possible to search variants of a value per line across multiple columns?
Yes.

To search across multiple columns change
VB
With Sheets ("Sheet2").Range("A:A")
to
VB
With Sheets ("Sheet2").Range("A:Z")
or whatever columns you want to search. You could also use
With Sheets ("Sheet2").UsedRange

To search for many values (variations) you could do something like this
VB
Sub FindIt()

    Dim wb As Workbook
    Set wb = ThisWorkbook   'Change this or ask the user which workbook to search

    Dim variations As New Collection
    Dim FindS As Variant
    FindS = "start"
    Do While Len(FindS) > 0
        FindS = InputBox("Enter a value you want to search for")
        If Len(FindS) > 0 Then
            variations.Add FindS
        End If
    Loop
    
    For Each FindS In variations
        Application.StatusBar = "Searching for " & FindS
        Dim Rng As Range
        Set Rng = wb.Sheets("Sheet2").Range("A1")
        Do While Not Rng Is Nothing
            With wb.Sheets("Sheet2").UsedRange
               Set Rng = .Find(What:=FindS, After:=Rng)
               If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                    Rng.Font.Color = vbRed
                    If MsgBox("Do you want to keep searching?", vbQuestion + vbYesNo + vbDefaultButton2, "Searching...") <> vbYes Then
                        Set Rng = Nothing
                    End If
                Else
                    MsgBox "Nothing Found"
                End If
            End With
        Loop
    Next
    Application.StatusBar = ""
End Sub
I will leave how to work out the permutations of a string for you to discover. My example code asks the user to enter the variations - if you work them out just populate that same collection.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900