i am re-writing this in trying to combine two posts into one and try to make mor

Question

i am re-writing this in trying to combine two posts into one and try to make more legible.

hi, am not sure if using best method, am using FIND which seems to be a separate physical step, when added to scroll row to get a destination to top of view, there is an extra screen jump compared to the use of a hyperlink formula for the same thing.

the hyperlink version has a number of drawbacks (a volatile function? when cut paste rows the row numbers can get messed up & cause an unsearchable Circular Reference, etc)

the find method seems to cause a problem in trying to use a wildcard for search for "dn" or ".dn." I get false hits on any cell that has: text dn text. for find wildcard: what:="*dn*"

it also causes the header to flash extraneous to the body flash of a screen update?.. (so if using the FIND method, is there a way for EVENTS FALSE to make the flash go away?)

trying to make a 1 line step to find a text, whole cell value for eg: "dn"

for the next value down from the cursor, something like:

      Cells.find(what:="dn", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, _
        searchorder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
  

which is ok for just searching for "dn", but i have interim lines: ".dn." i want to go to either: ".dn." or "dn"    and a wildcard will not work in: FIND for getting false hits.   

an example that would need to be corrected, but what trying to do for xlWhole:

(and if cannot stop the header extra screen flashing, might recommend using GOTO method below).

      Cells.find(what:="dn" OR what:=".dn.", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, _
        searchorder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate

Explanation / Answer

Sub abcd()

'Oct 06, 2014

Dim r1 As Range, r2 As Range
Set r1 = Cells.Find(What:="AB", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
Set r2 = Cells.Find(What:=".AB", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
If r1 Is Nothing And r2 Is Nothing Then MsgBox "nothing found"
If r1 Is Nothing And Not r2 Is Nothing Then r2.Select
If r2 Is Nothing And Not r1 Is Nothing Then r1.Select

Dim x As Long
If Not r2 Is Nothing And Not r2 Is Nothing Then
If r1.Column < r2.Column Then r1.Select
If r2.Column < r1.Column Then r2.Select
If r2.Column = r1.Column Then
x = WorksheetFunction.Min(r1.Row, r2.Row)
Cells(x, r1.Column).Select
End If
End If

End Sub

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.