如何在一行中找到一个值并用 VBA 返回列号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25060681/
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
How do I find a value in a row and return the column number with VBA?
提问by butterb3an
My excel sheet is filled with zeroes except for one cell in every row.
I want to find that cell and return the column.
For example: In the cell T616 is a value other than 0. May it be -15400.
I want to find that cell(T616) based on the row(616) and have the column returned(T). May it even be in a MsgBox.
This is my result of many tries and long Google-sessions:
除了每一行有一个单元格外，我的 Excel 工作表都填充了零。
我想找到那个单元格并返回该列。
例如：在单元格中 T616 是一个非 0 的值。它可能是 -15400。
我想根据行（616）找到该单元格（T616）并返回列（T）。甚至可以在 MsgBox 中。
这是我多次尝试和长时间 Google 会话的结果：
Public Function find_Column(lRange As Range, lValue As String) As Integer
Dim vCell As Range
For Each vCell In lRange.Cells
If vCell.Value = lValue Then
find_Column = vCell.Column
MsgBox (find_Column)
Exit Function
End If
Next vCell
End Function
I found this code somewhere and modified it a little bit, but I can't remember where. So thanks to the creator!
How do I search for a number other than 0?
I'm relatively new to VBA and don't really have an idea what I am doing. Sorry for my bad English (foreigner). I'd appreciate any help. Thank you!
我在某处找到了这段代码并对其进行了一些修改，但我不记得在哪里。所以感谢创作者！
如何搜索 0 以外的数字？
我对 VBA 比较陌生，并且真的不知道我在做什么。对不起，我的英语不好（外国人）。我很感激任何帮助。谢谢！
采纳答案by SCB
The <>symbol is the "Not Equal" Comparison. So if you wanted to check if a cell didn't equal 0, you would write
该<>标志是“不等于”比较。所以如果你想检查一个单元格是否不等于 0，你会写
If vCell.Value <> 0 Then
... ' rest of code here
In your problem:
在你的问题中：
Public Function find_Column(lRange As Range) As Integer
Dim vCell As Range
For Each vCell In lRange.Cells
If vCell.Value <> 0 Then
find_Column = vCell.Column
MsgBox (find_Column)
Exit Function
End If
Next vCell
End Function
Also since you are only checking against 0, you wouldn't need the extra lValue As Stringargument.
此外，由于您只检查 0，因此您不需要额外的lValue As String参数。
回答by AnalystCave.com
Try this:
尝试这个：
Public Function findNonZeroValueInColumn(lRange As Range) As Integer
Dim vCell As Range
For Each vCell In lRange.Cells
If vCell.Value <> 0 Then
find_Column = vCell.Column
Exit Function
End If
Next vCell
End Function
Sub ShowValue()
call MsgBox(findNonZeroValueInColumn(Range("A:A")))
End Sub
Remember that Functions are supposed to return values. Subs (Procedures) do not return values.
请记住，函数应该返回值。Subs (Procedures) 不返回值。
回答by Dave
As the other part of your question has been answered while I was typing, if you want to return the column letter instead of the number...
由于我在打字时已回答了您问题的另一部分，如果您想返回列字母而不是数字...
find_Column = Replace(Replace(vCell.Address, vCell.Row, ""), "$", "")
If this is what you want the function to return, you would have to change the type your function is returning to string
如果这是您希望函数返回的内容，则必须将函数返回的类型更改为字符串
