Search for exact match of string in excel row using VBA Macro

Question

How do I search for a string in one particular row in excel? the I have the row index in a long type variable.

Dim rowIndex As Long
rowIndex = // some value being set here using some code.

Now I need to check if a particular value exists in the row, whoose index is rowIndex.

If there is match, I need to get the column Index of the first matching cell.

I have tried using Match function, but I dont know how to pass the rowIndex variable in place of the cell range.

Dim colIndex As Long    
colIndex = Application.Match(colName, Range("B <my rowIndex here>: Z <my rowIndex here>"), 0)

Answer 1

Try this:

Sub GetColumns()

Dim lnRow As Long, lnCol As Long

lnRow = 3 'For testing

lnCol = Sheet1.Cells(lnRow, 1).EntireRow.Find(What:="sds", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

End Sub

Probably best not to use colIndex and rowIndex as variable names as they are already mentioned in the Excel Object Library.

Answer 2

This is not another code as you have already helped yourself; but for you to take a look at the performance when using Excel functions in VBA.

• Match() is faster for smaller number of search, but it isn't when it compared to a large number of data. And in that instance you may consider a variant array/dictionary/arrayList approach depending on if you want to sort, you want to have duplicates or not..

• Error handling on Match

PS: **On a latter note, if you wish to do pattern matching then you may consider ScriptingObject **Regex.