CODEWITHSUNDEEP
excelvbamatch
cmoe
cmoe

Reputation: 3

How to search with "Match" in a row?

How can I use the "Match" function in VBA for Excel to search for a given value in a row, if I don't want to search the entire row, but only from one cell to the end of the row to the right (compare screenshot). The column of the cell where the search is to be started, is defined by a variable. Actually I thought it would work with the following command, but an error message appears. If I replace the column variable (oldReadColumn) with a constant (e.g. 6), it works fine.

Dim oldReadColumn As Integer
Dim newReadColumn As Integer
Dim searchValue As String
searchValue = "C"

newReadColumn = Application.Match(searchValue, ThisWorkbook.Worksheets("Sheet1").Range(Cells(32, oldReadcolumn), Cells(32, 2000)), 0)

Also look at this Screenshot: The marked range should now be searched (from oldReadColumn + 1 to Column 2000)

Screenshot: Problem visually recreated in Excel:
Screenshot: Problem visually recreated in Excel

The answer can't be far away, but I just can't figure it out.

Upvotes: 0

Views: 60

Answers (1)

SJR
SJR

Reputation: 23081

You need to fully qualify all your ranges/cells (note all the dots), and hadn't defined oldReadColumn in your example.

This is untested but should I think work.

And declare newReadColumn as variant to allow for you not finding a match (which will return an error).

Dim oldReadColumn As Long
Dim newReadColumn As Variant
Dim searchValue As String

searchValue = "C"
oldReadColumn = 3

With ThisWorkbook.Worksheets("Sheet1")
    newReadColumn = Application.Match(searchValue, .Range(.Cells(32, oldReadColumn), .Cells(32, 2000)), 0)
End With

If IsNumeric(newReadColumn) Then
    'do stuff
Else
    MsgBox "Not found"
End If

Upvotes: 3

Related Questions