如何使用DataBodyRange获取行号

huangapple go评论105阅读模式
英文:

How to get row number with DataBodyRange

问题

我正在尝试获取我的 partNum 位于的行号。partNum 位于第 42 行。然而,当我运行我的代码时,我得到的是 41 而不是 42。我是按照我在这里看到的示例来做的,但后来遇到了这个问题。我不确定是否可能漏掉了什么?

英文:

I am trying to get the row number where my partNum is located at. The partNum is located at row 42. However, when I run my code, i get 41 instead. I was following an example I seen here but then I ran into this issue. I am not sure if maybe I am missing something?

Function FindRFIDCode(ByVal partNum As String) As Integer

    FindRFIDCode = 0
    Dim matchResult As Variant
    Dim tbl As ListObject
    Set tbl = Range("CombinedTapeInfo").ListObject
    
    matchResult = Application.Match(partNum, tbl.ListColumns(1).DataBodyRange, 0)
    
    If IsError(matchResult) Then
        FindRFIDCode = 0
    Else
        FindRFIDCode = matchResult
    End If
    
End Function

答案1

得分: 2

我是新来的,尝试引导你走向正确的方向。
如果你在引用表格,那么你应该使用表格名称或索引。结果41是表格行的索引号,数据主体范围,不包括表头。确保声明的变量与数据(字符串,整数)一致。
我编写了一些额外的代码来在D2中打印结果,并使用C2作为要查找的数字的输入范围。

Sub Test()
    Dim partNum As String
    partNum = ThisWorkbook.Worksheets(1).Range("C2")
    Dim i As Integer
    i = FindRFIDCode(partNum)
    ThisWorkbook.Worksheets(1).Range("D2") = i
End Sub

Function FindRFIDCode(ByVal partNum As String) As Integer
    FindRFIDCode = 0
    Dim matchResult As Variant
    Dim iRow As Integer
    Dim tbl As ListObject
    'Set tbl = Range("CombinedTapeInfo").ListObject
    Set tbl = ThisWorkbook.Worksheets(1).ListObjects("tbl_Fruits")
    matchResult = Application.Match(partNum, tbl.ListColumns(1).DataBodyRange, 0)
    iRow = tbl.ListColumns(1).DataBodyRange(matchResult).Row
    
    If IsError(matchResult) Then
        FindRFIDCode = 0
    Else
        FindRFIDCode = iRow
    End If
End Function

希望这有所帮助。

英文:

I'm new here and tried to guide you in the right direction.
If you are referencing to a table, then you should use the table name or index. The result 41 is the index number of the table row, the databodyrange, without the table header. Make sure that declared variables are in line with the data (string, integer).
I wrote some additional code to print the result in D2 and used C2 as input range for the number to be found.

Sub Test()
Dim partNum As String
    partNum = ThisWorkbook.Worksheets(1).Range("C2")
Dim i As Integer
    i = FindRFIDCode(partNum)
    ThisWorkbook.Worksheets(1).Range("D2") = i
End Sub

Function FindRFIDCode(ByVal partNum As String) As Integer

FindRFIDCode = 0
Dim matchResult As Variant
Dim iRow As Integer
Dim tbl As ListObject
'Set tbl = Range("CombinedTapeInfo").ListObject
Set tbl = ThisWorkbook.Worksheets(1).ListObjects("tbl_Fruits")
matchResult = Application.Match(partNum, tbl.ListColumns(1).DataBodyRange, 0)
iRow = tbl.ListColumns(1).DataBodyRange(matchResult).Row

If IsError(matchResult) Then
    FindRFIDCode = 0
Else
    FindRFIDCode = iRow
End If

End Function

huangapple
  • 本文由 发表于 2023年8月4日 21:52:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/76836582.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定