Move the active cell to the left in find next macro

[snoopy80]

Hello everyone,

I have a table with column A as index and column D alphabets as follow

A B C D
-----------------------------
1 a
2 b
3 c
4 d

and I have this macro:

Search = InputBox("Enter a letter for Search OR" & vbCrLf + vbCrLf & "Enter X to Cancel.", "Find What")
If (Search = "x") Or (Search = "") Then
Exit Sub
Else

Cells.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

End If

Now If I run this macro and enter "b", it will find the letter b in column D and so Cell D2 is activate. My question is when I enter "b" in the search, I want it to find b in column D but instead of activating cell D2, I want cell A2 (index column) to be active so I know b in located in that row. Thanks for helps.

 

[SkipVought]

Hi,

Dim rng as Range
...
Set rng = Cells.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
If not rng is nothing then
  Cells(rng.row, "A").Select
end if

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[PHV]

Dim R As Range
Set R = Cells.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not R Is Nothing Then Cells(R.Row, 1).Activate


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DaleWatson123321]

Sub Search_Letter()
Search = InputBox("Enter a letter for Search    OR" & vbCrLf + vbCrLf & "Enter X to Cancel.", "Find What")

If (Search = "x") Or (Search = "") Then
    Exit Sub
Else
    Application.ScreenUpdating = False
    Cells.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    ActiveCell.Offset(0, -3).Select
    Application.ScreenUpdating = True
End If

End Sub

Regards, Dale Watson

 

[snoopy80]

Thanks All for helps, it work great!