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.

Answer 3

Use worksheet.find (worksheet is your worksheet) and use the row-range for its range-object. You can get the rangeobject like: worksheet.rows(rowIndex) as example

Then give find the required parameters it should find it for you fine. If I recall correctly, find returns the first match per default. I have no Excel at hand, so you have to look up find for yourself, sorry

I would advise against using a for-loop it is more fragile and ages slower than find.

Answer 4

Never mind, I found the answer.

This will do the trick.

Dim colIndex As Long    
colIndex = Application.Match(colName, Range(Cells(rowIndex, 1), Cells(rowIndex, 100)), 0)