Searching and storing Column Number

[rita.michail]

Hi,

Is there a way in VB I can search for a word in an excel file, and
then once found I store the column number of the cell where the word
is found?


Thanks

 

[JW]

One way:
Sub oneWay()
Dim r As Long
Dim sString As String
sString = "fred"
On Error Resume Next
r = Cells.Find(What:=sString, After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
On Error GoTo 0
If r = 0 Then
MsgBox "not found"
Else
MsgBox r
End If
End Sub

 

[rita.michail]

Thanks,

I tried that and I keep getting 1 as my answer but that's not where
the word is found..

Any idea why?

 

[Dave Peterson]

Did you look through column A to see if your string was anywhere in that column?

If you did, you may want to post your code and some details about what's in your
worksheet and where.

 

[rita.michail]

My Macro is exactly as JW wrote it: nothing before or after... also,
there's only one occurance of the word.

Thanks

 

[Guest]

Sub oneWay()
Dim r As Range
Dim sString As String


sString = "fred" ' change this to the word you are looking for

set r = Cells.Find(What:=sString, _
After:=Cells(rows.count, columns.count), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If r is nothing then
MsgBox "not found"
Else
application.Goto r, true
MsgBox r.column
End If
End Sub

--
Regards,
Tom Ogilvy

My Macro is exactly as JW wrote it: nothing before or after... also,
there's only one occurance of the word.

Thanks