DrDonut DrDonut - 4 months ago 5x
Vb.net Question

Excel: Search for text in specific column

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

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:

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?


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)


End If

End Sub

enter image description here