检测列中的最后一个条目以搜索使用范围内的值 [英] Detect the last entry in a Column to search for a value within the used range

问题描述

我正在尝试检查值是否在一定范围内.如果出现该值，则该实体的相应数据将复制到另一张纸上.诀窍是必须动态确定，因为各输入工作表的列大小各不相同.在Java中，可以使用 hasNext()函数.我认为VBA最相似的功能是 Sheet1.Column.End(xlup).如何为vba中的do while循环的测试条件确定列尾的最佳方法?

I am trying to check if a value is in a certain range. If that value appears, the corresponding data to that enty is copied to another sheet. The trick is it has to be dynamically determined because the column size varies from input sheet to input sheet. In Java the hasNext() function can be used. I think that VBA's most similar function would be Sheet1.Column.End(xlup). How is the best way to determine the end of a column for the test condition of a do while loop in vba?

伪示例:

'This is part of a nested loop, this segment traverses the column looking for 'specified data.

Do While (!Sheets(inputSheetName).SyntaxToDetermineEndOfColumn))
     If(someCell = someValue)
          Copy values from the corresponding row to fields in newSheet
     End If
     Next r        'This increments the row within the current column
Loop
Next c             'This increments to the next column of data

推荐答案

假设我们有以下数据:

我们要在前两列中找到幸福，并在该行中检索 C 列的值:

We want to find happiness in the first two columns and retrieve the column C value in that row:

Sub LookingForHappiness()
    Dim i As Long, j As Long, N As Long, h As String
    h = "happiness"
    For i = 1 To 2
        N = Cells(Rows.Count, i).End(xlUp).Row
        For j = 1 To N
            If Cells(j, i).Value = h Then
                MsgBox Cells(j, "C").Value
                MsgBox Cells(j, i).Address(0, 0)
                Exit Sub
            End If
        Next j
    Next i
End Sub

这篇关于检测列中的最后一个条目以搜索使用范围内的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！