Excel:搜索特定列中的文本

In an excel sheet, I search for a keyword to mark the title of the column, that is the easy part. Now I need to search for another string inside this column. The point is that the same string is also present in other columns, but on different rows, so those need to be ignored. The only way I found to do so, is by the method described here: https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

在excel表中，我搜索一个关键字来标记列的标题，这是最简单的部分。现在我需要在这个列中搜索另一个字符串。关键是相同的字符串也存在于其他列中，但是在不同的行上，所以需要忽略这些字符串。我发现这样做的唯一方法是使用这里描述的方法:https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

When I try to find the string like this:

当我试图找到这样的弦时:

Dim moduleCol As Excel.Range = moduleSheet.Cells.Find("Module Naam")
Dim upstreamRow As Integer
With moduleSheet.Range(moduleCol, moduleSheet.Cells(moduleSheet.UsedRange.Row, moduleCol.Column))
    upstreamRow = .Find(upstreamEm).Row
End With

It finds the string, but in the wrong column (and therefore at the wrong row). In my example sheet, I know that I search in column 'D', so for testing purposes, I tried:

它找到字符串，但在错误的列中(因此在错误的行中)。在我的示例表中，我知道我在D列中搜索，因此为了测试目的，我尝试:

    Dim upstreamRow As Integer
    Dim testCol As Integer
    With moduleSheet.Range("d1:d500")
        Dim testCell As Excel.Range = .Find(upstreamEm)
        upstreamRow = testCell.Row
        testCol = testCell.Column
    End With

Now the string is not found at all, even though it is present in this column (at cell d14). What am I missing? Is there a better way to find it?

现在根本找不到字符串，即使它存在于这个列中(在单元格d14中)。我缺少什么?有没有更好的方法来找到它?

1 个解决方案

#1

Something like this should do the trick. It will find the first value, then set the find range for the second value to only look in the column where the first value was found. Obviously it will need to be tweaked to meet your needs, but it should get you there. Below the code is a screenshot of my dataset:

像这样的东西应该能起作用。它将找到第一个值，然后将第二个值的查找范围设置为只在找到第一个值的列中查找。显然，它需要调整以满足你的需要，但它应该能让你达到目标。下面是我的数据集的截图:

Sub test()

Dim fVal As String
Dim fRange As Range

fVal = "TEST2"

Set fRange = ActiveSheet.Cells.Find(What:=fVal, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

If Not fRange Is Nothing Then

    fVal = "B"

    Set fRange = ActiveSheet.Columns(Split(fRange.Address, "$")(1)).Find(What:=fVal, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    fRange.Select

End If

End Sub