Finding exact string match in excel string using VBA Macro

Question

How to search string in single line in excel? I have a 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 specific value exists in the row, whoose index - rowIndex.

If there is a match, I need to get the index index of the first comparable cell.

I tried using the match function, but I don’t know how to pass the rowIndex variable instead of a range of cells.

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

Answer 1

Try the following:

 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 

It is probably best not to use colIndex and rowIndex as variable names, as they were already mentioned in the Excel object library.

Answer 2

Use workheet.find (a worksheet is your worksheet) and use a range of rows for your range object. You can get rangeobject as: workheet.rows (rowIndex) as an example

Then find the options that it should find for you. If I remember correctly, find returns the first match by default. I don’t have Excel at hand, so you need to find for yourself, sorry

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

Answer 3

Nothing, 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) 

Answer 4

This is not another code, as you have already helped yourself; but to view the function when using Excel functions in VBA.

• Match() faster for less search, but this is not the case when it is compared with more data. And in this case, you can consider the variant array/dictionary/arrayList depending on whether you want to sort, you want to have duplicates or not.

• Match error handling

PS: ** In the latter case, if you want to make pattern matching , then you can consider ScriptingObject **Regex .