I have some data in Excel where I need to use VBA to select only rows (entire rows) that match specific text in any one of the columns per row. The data that will be copied into Excel is dynamic, such that the text I need to match won't always be in the same column and I have that part figured out, but the matching is selecting more rows than I want.
For simplicity, I want to match on the text "$ value 1" or "$ value 2" or "$ value 37", etc. The text that needs to be matched/found will always begin with "$ value" in this example (without quotes) and will end with any digit from 1 to 99999. I included a screenshot from Excel showing a basic example with 7 rows, but the actual data will be thousands of rows.
I have a VBA/macro that sort of works (below), but I'm running into an issue where I want to match on "$ value 1" and select only that specific row (i.e. first row in the example Excel data/screenshot), but it's also selecting rows that have "$ value 10" and "$ value 15" (2nd and 3rd rows in the example data). It's essentially selecting anything that BEGINS WITH "$ value 1". I want to match EXACTLY on the entire content of any cells where it finds "$ value 1". Likewise, the same thing happens if I want to select the row with "$ value 2", it also selects the row that has "$ value 25" in the example data.
When the macro runs, it asks a user to simply input any digit from 1 to 99999 and in the Instr() line I'm concatenating on the "$ value " portion before the digit that the user input. When a user inputs a 1 for example, I want the VBA code to only select the rows that match exactly on "$ value 1", which could be found in any of the columns that have data (happens to be in column C in the example data, but the same value could be in other columns in other rows). I don't want VBA to select the rows which have "$ value 10" or "$ value 15", etc. unless the user explicitly input 10 or 15.
I'm not sure if I need to do some kind of text comparison or if there's a better method, but being new to VBA I'm not sure how to accomplish what I need.
Sub select_rows_based_on_match()
Dim rng As Range
Dim cell As Object
Dim row As Range
Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select
Set rng = Selection
strInput = InputBox("Enter a value to match on:")
For Each cell In rng
If InStr(cell.Text, ("$ value " & strInput)) Then
If Not row Is Nothing Then
Set row = Union(row, cell.EntireRow)
Else
Set row = cell.EntireRow
End If
End If
Next
If row Is Nothing Then
MsgBox "There wwas no $ value " & strInput & " found"
Else
row.Select
End If
End Sub
Using Match()
would be faster than going cell-by-cell for each row:
Sub select_rows_based_on_match()
Dim rng As Range, row As Range, matchedRows As Range, strInput
strInput = InputBox("Enter a value to match on:")
If Len(strInput) = 0 Then Exit Sub
strInput = "$ value " & strInput
Set rng = ActiveSheet.Range("A1").CurrentRegion
For Each row In rng.Rows
'if no match is found then Match returns an error value
If Not IsError(Application.Match(strInput, row, 0)) Then
If Not matchedRows Is Nothing Then
Set matchedRows = Union(matchedRows, row)
Else
Set matchedRows = row
End If
End If
Next row
If matchedRows Is Nothing Then
MsgBox "There was no '" & strInput & "' found"
Else
matchedRows.Select
End If
End Sub