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)
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.
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)
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.
PS:
**On a latter note, if you wish to do pattern matching then you may consider ScriptingObject **Regex.
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.
Source: Stackoverflow.com