I'm trying to run the following Find command in VBA:

sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Row

Once I find the text, I need to parse out the contents of the cell, but
currently the text is not being found. I know the text is there and I have
tried recording a macro to see what steps Excel would use. I also know that
I lose the Intelli-Sense when I add the .Row onto it. If there's a better
way to do it, please let me know.

Thanx!

--
TFWBWY...A

Bryan,

It works fine for me. Are you sure that the item in column C doesn't have
trailing spaces in it, which would cause a mis-match.

--
HTH

Bob Phillips

"Bryan Dickerson" wrote in message
...
I'm trying to run the following Find command in VBA:

sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Row

Once I find the text, I need to parse out the contents of the cell, but
currently the text is not being found. I know the text is there and I
have
tried recording a macro to see what steps Excel would use. I also know
that
I lose the Intelli-Sense when I add the .Row onto it. If there's a better
way to do it, please let me know.

Thanx!

--
TFWBWY...A

xlpart should take care of the trailing spaces--but not embedded spaces--or
other typos!

Bob Phillips wrote:

Bryan,

It works fine for me. Are you sure that the item in column C doesn't have
trailing spaces in it, which would cause a mis-match.

--
HTH

Bob Phillips

"Bryan Dickerson" wrote in message
...
I'm trying to run the following Find command in VBA:

sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Row

Once I find the text, I need to parse out the contents of the cell, but
currently the text is not being found. I know the text is there and I
have
tried recording a macro to see what steps Excel would use. I also know
that
I lose the Intelli-Sense when I add the .Row onto it. If there's a better
way to do it, please let me know.

Thanx!

--
TFWBWY...A



--

Dave Peterson

"eligible for rebate" is actually the start of the phrase in the contents of
the cell--the full contents might be something like "Eligible for rebate
$600 if order by 12/01/05". I made sure that the MatchCase is false so that
if they don't capitalize it, then it should still be found. I tried
searching for just "for rebate" and still nothing. I keep thinking it's the
".Row" property on the end, but if it works for you guys, then maybe it is
acceptable. What other things would you guys try to see what is wrong or to
perhaps do it another way?

"Dave Peterson" wrote in message
...
xlpart should take care of the trailing spaces--but not embedded
spaces--or
other typos!

Bob Phillips wrote:

Bryan,

It works fine for me. Are you sure that the item in column C doesn't have
trailing spaces in it, which would cause a mis-match.

--
HTH

Bob Phillips

"Bryan Dickerson" wrote in message
...
I'm trying to run the following Find command in VBA:

sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Row

Once I find the text, I need to parse out the contents of the cell, but
currently the text is not being found. I know the text is there and I
have
tried recording a macro to see what steps Excel would use. I also know
that
I lose the Intelli-Sense when I add the .Row onto it. If there's a
better
way to do it, please let me know.

Thanx!

--
TFWBWY...A



--

Dave Peterson

As much of a revelation as it is, I found something that works, but I'm not
too secure with it and that is shortening the string to just "eligible".
I'm not too secure with it because in the example I was working with just a
few rows down there was another cell that also had "eligible" in it, so I
would feel better if I could get "eligible for rebate" to work, but I will
go with this for now. I would still appreciate any comments that anyone
might have.

"Bryan Dickerson" wrote in message
...
"eligible for rebate" is actually the start of the phrase in the contents
of the cell--the full contents might be something like "Eligible for
rebate $600 if order by 12/01/05". I made sure that the MatchCase is
false so that if they don't capitalize it, then it should still be found.
I tried searching for just "for rebate" and still nothing. I keep
thinking it's the ".Row" property on the end, but if it works for you
guys, then maybe it is acceptable. What other things would you guys try
to see what is wrong or to perhaps do it another way?

"Dave Peterson" wrote in message
...
xlpart should take care of the trailing spaces--but not embedded
spaces--or
other typos!

Bob Phillips wrote:

Bryan,

It works fine for me. Are you sure that the item in column C doesn't
have
trailing spaces in it, which would cause a mis-match.

--
HTH

Bob Phillips

"Bryan Dickerson" wrote in message
...
I'm trying to run the following Find command in VBA:

sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Row

Once I find the text, I need to parse out the contents of the cell,
but
currently the text is not being found. I know the text is there and I
have
tried recording a macro to see what steps Excel would use. I also
know
that
I lose the Intelli-Sense when I add the .Row onto it. If there's a
better
way to do it, please let me know.

Thanx!

--
TFWBWY...A



--

Dave Peterson

Is that "eligible for rebate" actually in the cell?

It's not part of some custom formatting, is it?

Can you find one of those cells and put this in an adjacent (empty cell):

=countif(c99,"*eligible for rebate*")
(change c99 to the cell's address)

What do you get?

I'm still guessing it's a typo!



Bryan Dickerson wrote:

As much of a revelation as it is, I found something that works, but I'm not
too secure with it and that is shortening the string to just "eligible".
I'm not too secure with it because in the example I was working with just a
few rows down there was another cell that also had "eligible" in it, so I
would feel better if I could get "eligible for rebate" to work, but I will
go with this for now. I would still appreciate any comments that anyone
might have.

"Bryan Dickerson" wrote in message
...
"eligible for rebate" is actually the start of the phrase in the contents
of the cell--the full contents might be something like "Eligible for
rebate $600 if order by 12/01/05". I made sure that the MatchCase is
false so that if they don't capitalize it, then it should still be found.
I tried searching for just "for rebate" and still nothing. I keep
thinking it's the ".Row" property on the end, but if it works for you
guys, then maybe it is acceptable. What other things would you guys try
to see what is wrong or to perhaps do it another way?

"Dave Peterson" wrote in message
...
xlpart should take care of the trailing spaces--but not embedded
spaces--or
other typos!

Bob Phillips wrote:

Bryan,

It works fine for me. Are you sure that the item in column C doesn't
have
trailing spaces in it, which would cause a mis-match.

--
HTH

Bob Phillips

"Bryan Dickerson" wrote in message
...
I'm trying to run the following Find command in VBA:

sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Row

Once I find the text, I need to parse out the contents of the cell,
but
currently the text is not being found. I know the text is there and I
have
tried recording a macro to see what steps Excel would use. I also
know
that
I lose the Intelli-Sense when I add the .Row onto it. If there's a
better
way to do it, please let me know.

Thanx!

--
TFWBWY...A



--

Dave Peterson



--

Dave Peterson

I get a right-justified "1" (without the quotes, of course). What does that
mean?

"Dave Peterson" wrote in message
...
Is that "eligible for rebate" actually in the cell?

It's not part of some custom formatting, is it?

Can you find one of those cells and put this in an adjacent (empty cell):

=countif(c99,"*eligible for rebate*")
(change c99 to the cell's address)

What do you get?

I'm still guessing it's a typo!



Bryan Dickerson wrote:

As much of a revelation as it is, I found something that works, but I'm
not
too secure with it and that is shortening the string to just "eligible".
I'm not too secure with it because in the example I was working with just
a
few rows down there was another cell that also had "eligible" in it, so I
would feel better if I could get "eligible for rebate" to work, but I
will
go with this for now. I would still appreciate any comments that anyone
might have.

"Bryan Dickerson" wrote in message
...
"eligible for rebate" is actually the start of the phrase in the
contents
of the cell--the full contents might be something like "Eligible for
rebate $600 if order by 12/01/05". I made sure that the MatchCase is
false so that if they don't capitalize it, then it should still be
found.
I tried searching for just "for rebate" and still nothing. I keep
thinking it's the ".Row" property on the end, but if it works for you
guys, then maybe it is acceptable. What other things would you guys
try
to see what is wrong or to perhaps do it another way?

"Dave Peterson" wrote in message
...
xlpart should take care of the trailing spaces--but not embedded
spaces--or
other typos!

Bob Phillips wrote:

Bryan,

It works fine for me. Are you sure that the item in column C doesn't
have
trailing spaces in it, which would cause a mis-match.

--
HTH

Bob Phillips

"Bryan Dickerson" wrote in message
...
I'm trying to run the following Find command in VBA:

sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Row

Once I find the text, I need to parse out the contents of the cell,
but
currently the text is not being found. I know the text is there
and I
have
tried recording a macro to see what steps Excel would use. I also
know
that
I lose the Intelli-Sense when I add the .Row onto it. If there's a
better
way to do it, please let me know.

Thanx!

--
TFWBWY...A



--

Dave Peterson



--

Dave Peterson

That means that "eligible for rebate" is in that cell.

And all my theories about typos goes flying out the window!.

Maybe it's the way you wrote the code.

sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate", _
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Row

Could cause errors if oWksht isn't the activesheet or the activecell isn't in
column C.

I think I'd use something like this:

Option Explicit
Sub testme()

Dim sERRow As Long
Dim FoundCell As Range
Dim oWkSht As Worksheet
Dim myStr As String

myStr = "eligible for rebate"

Set oWkSht = Worksheets("Sheet1")

With oWkSht.Range("C:C")
Set FoundCell = .Cells.Find(What:=myStr, _
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
End With

If FoundCell Is Nothing Then
MsgBox "Oh, oh. What happens here?"
Exit Sub '???
End If

sERRow = FoundCell.Row

MsgBox sERRow

End Sub

Bryan Dickerson wrote:

I get a right-justified "1" (without the quotes, of course). What does that
mean?

"Dave Peterson" wrote in message
...
Is that "eligible for rebate" actually in the cell?

It's not part of some custom formatting, is it?

Can you find one of those cells and put this in an adjacent (empty cell):

=countif(c99,"*eligible for rebate*")
(change c99 to the cell's address)

What do you get?

I'm still guessing it's a typo!



Bryan Dickerson wrote:

As much of a revelation as it is, I found something that works, but I'm
not
too secure with it and that is shortening the string to just "eligible".
I'm not too secure with it because in the example I was working with just
a
few rows down there was another cell that also had "eligible" in it, so I
would feel better if I could get "eligible for rebate" to work, but I
will
go with this for now. I would still appreciate any comments that anyone
might have.

"Bryan Dickerson" wrote in message
...
"eligible for rebate" is actually the start of the phrase in the
contents
of the cell--the full contents might be something like "Eligible for
rebate $600 if order by 12/01/05". I made sure that the MatchCase is
false so that if they don't capitalize it, then it should still be
found.
I tried searching for just "for rebate" and still nothing. I keep
thinking it's the ".Row" property on the end, but if it works for you
guys, then maybe it is acceptable. What other things would you guys
try
to see what is wrong or to perhaps do it another way?

"Dave Peterson" wrote in message
...
xlpart should take care of the trailing spaces--but not embedded
spaces--or
other typos!

Bob Phillips wrote:

Bryan,

It works fine for me. Are you sure that the item in column C doesn't
have
trailing spaces in it, which would cause a mis-match.

--
HTH

Bob Phillips

"Bryan Dickerson" wrote in message
...
I'm trying to run the following Find command in VBA:

sERRow = oWkSht.Columns("C").Find(What:="eligible for rebate",
After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=
_
xlNext, MatchCase:=False, SearchFormat:=False).Row

Once I find the text, I need to parse out the contents of the cell,
but
currently the text is not being found. I know the text is there
and I
have
tried recording a macro to see what steps Excel would use. I also
know
that
I lose the Intelli-Sense when I add the .Row onto it. If there's a
better
way to do it, please let me know.

Thanx!

--
TFWBWY...A



--

Dave Peterson



--

Dave Peterson

--

Dave Peterson