Excel/VBA - 返回整数的函数

Excel/VBA - Function to return integer

提问by user559142

I would like a function to return the index of a column given the rowIndex and a Cell Value:

我想要一个函数来返回给定 rowIndex 和一个单元格值的列的索引：

'Get index of column given a heading name
Function FindColumnIndex(name As String, rowNumber As Integer) As Integer
    Dim index As Integer
    index = 1
    Dim found As Boolean
    found = False

    Do While found = False
        If Cells(rowNumber, index).Value = name Then
            FindColumnIndex = index
            Exit Do
        End If

        index = index + 1
    Loop

    FindColumnIndex = index


End Function

I will then assign this to a value:

然后我将其分配给一个值：

Dim colIndex As Integer
colIndex = FindColumnIndex("Physical or Virtual", 2)

Problem is this isn't working - and I am sure my function is incorrect - Anyone know what I'm doing wrong?

问题是这不起作用 - 我确定我的功能不正确 - 有谁知道我做错了什么？

回答by nullrevolution

One thing I spotted off the bat:

我立即发现的一件事：

If Cells(row, index).Value = name Then

The variable passed to the function is named rowNumber, not row. Change that line to:

传递给函数的变量名为rowNumber，而不是row。将该行更改为：

If Cells(rowNumber, index).Value = name Then

---

---

edit:

编辑：

One other thing to note is that your function never actually stops on its own. The only reason it ever ends is because it runs into an application defined error when trying to read column 16385 (because Excel is limited to 16384 columns*), which immediately kills the function, returning a #VALUEerror.

需要注意的另一件事是，您的函数实际上从未自行停止。它结束的唯一原因是它在尝试读取第 16385 列时遇到应用程序定义的错误（因为 Excel 限制为 16384 列*），这会立即终止该函数，返回#VALUE错误。

The following revision prevents that and returns a -1 if the requested column name is not found:

以下修订版防止了这种情况，如果未找到请求的列名，则返回 -1：

Option Explicit

Function FindColumnIndex(name As String, rowNumber As Integer) As Integer
    Dim index As Integer
    index = 1
    Dim found As Boolean
    found = False

    FindColumnIndex = -1

    Do While found = False
        If Cells(rowNumber, index).Value = name Then
            FindColumnIndex = index
            found = True
        End If

        index = index + 1
        If index > 16384 Then Exit Do
    Loop

End Function

[ * Excel 2007 is thus limited, anyway. I have no idea if newer versions have larger limits or not.]

[ * 无论如何，Excel 2007 是有限的。我不知道新版本是否有更大的限制。]

回答by qPCR4vir

   If Cells(row, index).Value = name Then

did you mean:

你的意思是：

If Cells(rowNumber , index).Value = name Then

Anyway, there are functions to do this, something like MATCH, INDEX & Co.

无论如何，有一些函数可以做到这一点，比如MATCH、 INDEX & Co。