获取第一行匹配字符串的列号。excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26352039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Column number of matching string in first row. Excel VBA
提问by De De De De
I'm trying to create a function to get the column number of the cell based on matching string being inserted. If there is two match being found in the first row, I would like to return the last match instead. For example, "TotalSalary Jan" and "TotalSalary Feb". With "TotalSalary" inserted as parameter, I'll be getting the column number for "TotalSalary Feb". My code:
我正在尝试创建一个函数来根据插入的匹配字符串获取单元格的列号。如果在第一行找到两个匹配项，我想返回最后一个匹配项。例如，“TotalSalary Jan”和“TotalSalary Feb”。将“TotalSalary”作为参数插入后，我将获得“TotalSalary Feb”的列号。我的代码：
Private Function GetColumnNumber(name As String) As Integer
Dim res As Object, ret As Integer
Set res = Sheets("Unified").Cells(1, 1).EntireRow.Find(What:=name, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
If Not res Is Nothing Then
ret = res.Column
Do
Set res = .FindNext(res)
ret = res.Column
Loop While Not res Is Nothing And res.Column <> ret
GetColumnNumber = ret
End If
End Function
By the way, the code is not working properly. res object could not findnext for column number.
顺便说一下，代码不能正常工作。res 对象找不到列号的下一个。
回答by Jur Pertin
Try this and let me know.
试试这个，让我知道。
Private Function GetColumnNumber(strKeyword As String) As Integer
私有函数 GetColumnNumber(strKeyword As String) As Integer
Dim rngColLoop As Range
Dim intCounter As Integer
Dim intColNum As Integer
Dim intPrevious As Integer
Dim intCurrent As Integer
lngCounter = 0
With Sheets("Unified").Cells(1, 1).EntireRow
For Each rngColLoop In .Columns
If Trim(rngColLoop) <> "" Then
If InStr(1, UCase(Trim(rngColLoop)), UCase(Trim(strKeyword))) > 0 Then
intCounter = intCounter + 1
If intCounter = 1 Then
intPrevious = rngColLoop.Column
intCurrent = rngColLoop.Column
Else
intPrevious = intCurrent
intCurrent = rngColLoop.Column
End If
End If
End If
Next rngColLoop
End With
If intCounter = 0 Then
GetColumnNumber = 0
Else
GetColumnNumber = intCurrent
End If
Set rngColLoop = Nothing
End Function
结束函数
回答by Mark Price
I used a different approach, by changing the direction of the search you can find the last instance with a single find method.
我使用了不同的方法，通过更改搜索方向，您可以使用单个 find 方法找到最后一个实例。
Private Function GetColumnNumber(name As String) As Integer
私有函数 GetColumnNumber(name As String) As Integer
Dim res As Object
Set res = Sheets("Unified").Cells(1, 1).EntireRow.Find(What:=name _
, LookIn:=xlValues _
, LookAt:=xlPart _
, SearchOrder:=xlByColumns _
, SearchDirection:=xlPrevious _
, MatchCase:=False)
If res Is Nothing Then
GetColumnNumber = 0
Else
GetColumnNumber = res.Column
End If
End Function
结束函数
回答by De De De De
I have created another method to add on in this function. This is working btw... I'm using the Variant Array to get Column Number.
我已经创建了另一种方法来添加到这个函数中。顺便说一句，这是有效的......我正在使用 Variant Array 来获取列号。
Private Function GetColumnNumber(name As String) As Integer
Dim play As Variant, j As Long, Current As Integer
Set play = Sheets("Unified").Range("1:1")
For i = 1 To play.Columns.Count
If InStr(play(1, i), name) > 0 Then
Current = i
End If
Next i
GetColumnNumberArray = Current
End Function
I have a look at this article and it is very helpful for optimizing your code. http://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/Apparently, the usage of find and match is very demanding command for your computer.
我看看这篇文章，它对优化您的代码非常有帮助。http://fastexcel.wordpress.com/2011/10/26/match-vs-vs-find-vs-variant-array-vba-performance-shootout/显然，查找和匹配的使用对您的计算机来说是非常苛刻的命令。
