Simple find command

[kckay]

I am trying to find the value "DO" in the B15:B40 range using VBA.

Should be straightforward, but I end up either not working it or running against the entire worksheet.

 

[jmthompson]

What do you want to do once it's found?

 

[andiemac]

This will find the first DO. If you want to find all you would have to make some type of loop.

Sub Find_DO()
    Range("B15:B40").Select
    Selection.Find(What:="DO", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
End Sub

 

[kckay]

I'm looking for the row that this particular value ("DO") resides.

 

[andiemac]

This tells you the row of first occurance of DO, but I'm still not clear on what your end goal is.

Sub Find_DO()
Dim MyRow
    Range("B15:B40").Select
    Selection.Find(What:="DO", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    MyRow = ActiveCell.Row
    MsgBox MyRow
End Sub

 

[kckay]

Thank you very much for the assistance.

"DO" stands for Designated Operator and is a row of variable length. "DO" is the only common cell value of a series of records generated from an SQL server. Since the values are variable length and variable rows within records, now that I know the row, I can extract the data I need.

 

[brettdj]

Same general idea but
- you don't need to select the range to search it
- if you hold the result in a variable range then you can test whether it is found or not (whereas the code above will error if there is no "DO")

The code below searches for DO anywhere in a cell within B15:B40
If DO should be the entire cell contents, not just a partial match then use

Set rng1 = Range("b15:b40").Find(strFind, , xlValues, xlWhole)

Cheers

Dave

Sub FindMe()
    Dim strFind As String
    Dim rng1 As Range
    strFind = "DO"
    Set rng1 = Range("b15:b40").Find(strFind, , xlValues, xlPart)
    If Not rng1 Is Nothing Then
        MsgBox "row is " & rng1.Row
    Else
        MsgBox strFind & " not found"
    End If
End Sub