使用 VBA 宏在 excel 行中搜索字符串的精确匹配

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

提问by Manas Saha

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

如何在excel中的特定行中搜索字符串？I 在 long 类型变量中有行索引。

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.

现在我需要检查行中是否存在特定值，其索引是 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.

我曾尝试使用 Match 函数，但我不知道如何传递 rowIndex 变量来代替单元格范围。

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

回答by MattCrum

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.

最好不要使用 colIndex 和 rowIndex 作为变量名，因为它们已经在 Excel 对象库中提到过。

回答by bonCodigo

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

这不是您已经帮助过自己的另一个代码；但让您看看在 VBA 中使用 Excel 函数时的性能。

• 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/arrayListapproach depending on if you want to sort, you want to have duplicates or not..

• Error handling on Match

• Match()对于较少数量的搜索，速度更快，但与大量数据相比则不然。在这种情况下，您可能会考虑一种variant array/dictionary/arrayList方法，具体取决于您是否要排序，是否要重复。

• Match 上的错误处理

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

PS：**在后面的说明中，如果您想这样做，pattern matching那么您可以考虑ScriptingObject **Regex.

回答by Christian Sauer

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

使用 worksheet.find （工作表是您的工作表）并使用行范围作为其范围对象。您可以获取范围对象，例如：worksheet.rows(rowIndex) 作为示例

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

然后给 find 所需的参数，它应该为您找到它。如果我没记错的话， find 会默认返回第一个匹配项。我手头没有Excel，所以你必须自己查找，抱歉

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

我建议不要使用 for 循环，它比 find 更脆弱，老化速度更慢。

回答by Manas Saha

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)