Solved: Selection.Find error

[DeadKing]

Hello.

I'm prety new to this VBA thing so I'm probably doing some stupid mistake, but I'm just not able to find out what it is.

I'm trying to find text from one sheet in the another sheet. I know where it should be (column) and I need to get row number of cell with the finded text.

I've tried some different ways to do it, but I allways end up with an error. Strange is that the error does not occure when I go through the code step by step...

First I've tried the most obvious way:

dim work as string
dim x as long
work = "My Work Sheet"
Sheets(work).Select
Columns("S:S").Select
x = Selection.Find(What:=stext, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).row

And finaly I've ended up with something like this (what is not working in any way):

dim a as range
dim work as string
dim x as long
work = "My Work Sheet"
Sheets(work).Select
Columns("S:S").Select
Set a = Selection.Find(What:=stext, After:=ActiveSheet.Cells("S1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
x = a.row

Thanks in advance for any kind of help.

[DeadKing]

Well, I've found my way around using loops, but it takes A LOT of time to process... So, if someone could help me with the Find function, I'd be happy.

[Bob Phillips]

This works fine for me



Dim a As Range
Dim work As String
Dim x As Long

With Worksheets("My Work Sheet").Columns("S:S")

Set a = .Find(What:=stext, _
After:=ActiveSheet.Range("S1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
x = a.Row
End With

[mdmackillop]

FYI the issue is here
Columns("S:S").Select
x = Selection.Find(What:=stext, After:=ActiveCell
The Find will fail if the ActiveCell is not in Column S

[DeadKing]

Ah, I see... Thank you for your help.