如何找到一行中的值并将列号与VBA返回?

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表除了每一行的一个单元格外，都是0。我想找到那个单元格并返回列。例如:在单元格中，T616不是0。可能是-15400年。我希望根据行(616)查找单元格(T616)，并返回列(T)。它甚至可能在MsgBox中。这是我的许多尝试和长期谷歌会议的结果:

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比较陌生，也不知道自己在做什么。对不起，我的英语不好。我感谢任何帮助。谢谢你！

3 个解决方案

#1

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 String argument.

而且，因为您只检查0，所以不需要额外的lValue作为字符串参数。

#2

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(过程)不返回值。

#3

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

如果这是你想要函数返回的，你必须改变函数返回到字符串的类型

#1