How do I Limit a VBA Find Command to a single column?

[Lidsavr]

I am running Excel 2007 and writing code that 2003 users will run.

I have the following code looking for any hyphens (-) in Column E. If it doesn't find any, it automatically starts looking at other columns. How can I limit the search to only column E?

Here is the code that I am using

    Range("E:E").Select
    Cells.Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

Thank you for your help in advance.

Charles

 

[JackDanIce]

Try:

Range("E:E").Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, _
  LookAt:= xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
  MatchCase:= False, SearchFormat:=False).Activate

Your code is Cells.Find which means even if Column E is selected, the code is then searching through all the Cells (i.e. the whole worksheet)

 

[Lidsavr]

JackDanIce,
Thank you. I understand what you mean now that I have seen your code. It works.

I have a question though. If it does not find anything, I want the code to Exit.

Do you know how to do that?

 

[JackDanIce]

Try:

On Error Goto notfound
Range("E:E").Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, _
  LookAt:= xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
  MatchCase:= False, SearchFormat:=False).Activate


notfound:
Msgbox "- is not found in column E, macro stopping"
Exit Sub

 

[Lidsavr]

JackDanIce,
Thank you. The code is working perfectly now!

I've have and continue to learn much from people like you on this forum. Thank you for sharing your knowledge.

Charles

 

[JackDanIce]

You're welcome, glad I could help, thanks for the feedback too.

 

[Scott Huish]

There is another way to do that that doesn't involve GOTO.
Drop the Activate, assign it to a range and see if it returned anything. Then utilize the range variable to do what you want.

Sub test()
Dim c As Range
Set c = Range("E:E").Find(What:="-", After:=ActiveCell, LookIn:=xlFormulas, _
  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
  MatchCase:=False)
If c Is Nothing Then
    MsgBox "Not Found"
    Exit Sub
End If
c.Select
End Sub