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
With Sheets ("Sheet2").Range("A:A")
to
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
Sub FindIt()
Dim wb As Workbook
Set wb = ThisWorkbook
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.