I am trying to use the Find method in VBA to identify whether a cell contains
a phrase (in this case "_SHARE") such that if it does a specific piece of
code is run. I am using the following code:

With Sheets("CHART_SHEET").Range("b63")
Set C = .Find("_SHARE", LookIn:=xlValues, LookAt:=xlPart)
End With
If Not C Is Nothing Then GoSub 3000

However, even when cell b63 has "_SHARE" as part of its value, this still
returns c as equal to Nothing.

One other point that may be relevant is that the cell b63 is based on a
vlookup function.

Thanks in advance,


Ian

Ian,
If you are only checking in a single cell, why not use InStr ?
If Instr(Sheets("CHART_SHEET").Range("B63").Value,"_S HARE")0 then

NickHK

"IanC" ...
I am trying to use the Find method in VBA to identify whether a cell
contains
a phrase (in this case "_SHARE") such that if it does a specific piece of
code is run. I am using the following code:

With Sheets("CHART_SHEET").Range("b63")
Set C = .Find("_SHARE", LookIn:=xlValues, LookAt:=xlPart)
End With
If Not C Is Nothing Then GoSub 3000

However, even when cell b63 has "_SHARE" as part of its value, this still
returns c as equal to Nothing.

One other point that may be relevant is that the cell b63 is based on a
vlookup function.

Thanks in advance,


Ian

Thanks. That will do fine.

Is there any reason why Find should not work in this instance?


Ian

"NickHK" wrote:

Ian,
If you are only checking in a single cell, why not use InStr ?
If Instr(Sheets("CHART_SHEET").Range("B63").Value,"_S HARE")0 then

NickHK

"IanC" ...
I am trying to use the Find method in VBA to identify whether a cell
contains
a phrase (in this case "_SHARE") such that if it does a specific piece of
code is run. I am using the following code:

With Sheets("CHART_SHEET").Range("b63")
Set C = .Find("_SHARE", LookIn:=xlValues, LookAt:=xlPart)
End With
If Not C Is Nothing Then GoSub 3000

However, even when cell b63 has "_SHARE" as part of its value, this still
returns c as equal to Nothing.

One other point that may be relevant is that the cell b63 is based on a
vlookup function.

Thanks in advance,


Ian

Ian,
Your code works for me.

By the way, do you really need GoSub ?
Can you not structure your code to avoid this ?

NickHK



"IanC" wrote in message
...
Thanks. That will do fine.

Is there any reason why Find should not work in this instance?


Ian

"NickHK" wrote:

Ian,
If you are only checking in a single cell, why not use InStr ?
If Instr(Sheets("CHART_SHEET").Range("B63").Value,"_S HARE")0 then

NickHK

"IanC"
...
I am trying to use the Find method in VBA to identify whether a cell
contains
a phrase (in this case "_SHARE") such that if it does a specific piece
of
code is run. I am using the following code:

With Sheets("CHART_SHEET").Range("b63")
Set C = .Find("_SHARE", LookIn:=xlValues, LookAt:=xlPart)
End With
If Not C Is Nothing Then GoSub 3000

However, even when cell b63 has "_SHARE" as part of its value, this
still
returns c as equal to Nothing.

One other point that may be relevant is that the cell b63 is based on
a
vlookup function.

Thanks in advance,


Ian

Nick,

I have found that the code works occasionally, but mostly not. It also
fails to work on both my PCs runiing different Excels. I have a suspicion
that it relates in some way to the look up, because if I hard code in the
phrase "_SHARE" it works every time. The sheet in question is re-calculated
in the line immediately before this code.

Yes, the Gosub could be changed. Does this slow down the code?

Ian

"NickHK" wrote:

Ian,
Your code works for me.

By the way, do you really need GoSub ?
Can you not structure your code to avoid this ?

NickHK



"IanC" wrote in message
...
Thanks. That will do fine.

Is there any reason why Find should not work in this instance?


Ian

"NickHK" wrote:

Ian,
If you are only checking in a single cell, why not use InStr ?
If Instr(Sheets("CHART_SHEET").Range("B63").Value,"_S HARE")0 then

NickHK

"IanC"
...
I am trying to use the Find method in VBA to identify whether a cell
contains
a phrase (in this case "_SHARE") such that if it does a specific piece
of
code is run. I am using the following code:

With Sheets("CHART_SHEET").Range("b63")
Set C = .Find("_SHARE", LookIn:=xlValues, LookAt:=xlPart)
End With
If Not C Is Nothing Then GoSub 3000

However, even when cell b63 has "_SHARE" as part of its value, this
still
returns c as equal to Nothing.

One other point that may be relevant is that the cell b63 is based on
a
vlookup function.

Thanks in advance,


Ian

Ian,
From description, I'm sure the code does work, but either the cell's value
or the search value are not what you think at that time.
Put in a few Debug.print statements to check their values before you run the
..Find.

As for GoSub, it is generally considered a bad construct nowadays, as you
can easily replace with more readable code ; replace it with a function call
or some branching.

NickHK

"IanC" ...
Nick,

I have found that the code works occasionally, but mostly not. It also
fails to work on both my PCs runiing different Excels. I have a suspicion
that it relates in some way to the look up, because if I hard code in the
phrase "_SHARE" it works every time. The sheet in question is
re-calculated
in the line immediately before this code.

Yes, the Gosub could be changed. Does this slow down the code?

Ian

"NickHK" wrote:

Ian,
Your code works for me.

By the way, do you really need GoSub ?
Can you not structure your code to avoid this ?

NickHK



"IanC" wrote in message
...
Thanks. That will do fine.

Is there any reason why Find should not work in this instance?


Ian

"NickHK" wrote:

Ian,
If you are only checking in a single cell, why not use InStr ?
If Instr(Sheets("CHART_SHEET").Range("B63").Value,"_S HARE")0 then

NickHK

"IanC"
...
I am trying to use the Find method in VBA to identify whether a cell
contains
a phrase (in this case "_SHARE") such that if it does a specific
piece
of
code is run. I am using the following code:

With Sheets("CHART_SHEET").Range("b63")
Set C = .Find("_SHARE", LookIn:=xlValues, LookAt:=xlPart)
End With
If Not C Is Nothing Then GoSub 3000

However, even when cell b63 has "_SHARE" as part of its value, this
still
returns c as equal to Nothing.

One other point that may be relevant is that the cell b63 is based
on
a
vlookup function.

Thanks in advance,


Ian

Nick,

Obviously showing my age with the Gosub!

Your solution works fine and with less code, so I will be grateful for, and
use, that.

Thanks once again.

Ian

"NickHK" wrote:

Ian,
From description, I'm sure the code does work, but either the cell's value
or the search value are not what you think at that time.
Put in a few Debug.print statements to check their values before you run the
..Find.

As for GoSub, it is generally considered a bad construct nowadays, as you
can easily replace with more readable code ; replace it with a function call
or some branching.

NickHK

"IanC" ...
Nick,

I have found that the code works occasionally, but mostly not. It also
fails to work on both my PCs runiing different Excels. I have a suspicion
that it relates in some way to the look up, because if I hard code in the
phrase "_SHARE" it works every time. The sheet in question is
re-calculated
in the line immediately before this code.

Yes, the Gosub could be changed. Does this slow down the code?

Ian

"NickHK" wrote:

Ian,
Your code works for me.

By the way, do you really need GoSub ?
Can you not structure your code to avoid this ?

NickHK



"IanC" wrote in message
...
Thanks. That will do fine.

Is there any reason why Find should not work in this instance?


Ian

"NickHK" wrote:

Ian,
If you are only checking in a single cell, why not use InStr ?
If Instr(Sheets("CHART_SHEET").Range("B63").Value,"_S HARE")0 then

NickHK

"IanC"
...
I am trying to use the Find method in VBA to identify whether a cell
contains
a phrase (in this case "_SHARE") such that if it does a specific
piece
of
code is run. I am using the following code:

With Sheets("CHART_SHEET").Range("b63")
Set C = .Find("_SHARE", LookIn:=xlValues, LookAt:=xlPart)
End With
If Not C Is Nothing Then GoSub 3000

However, even when cell b63 has "_SHARE" as part of its value, this
still
returns c as equal to Nothing.

One other point that may be relevant is that the cell b63 is based
on
a
vlookup function.

Thanks in advance,


Ian

Ian,
It is quite surprising how much BASIC is still supported in VB/VBA.
A few weeks ago I had some 20+year old basic code sent me (to calculate the
sun rise at any location on a day of the year) that (to me) was a complete
mess of GoSubs, Returns etc. Apart from changing a couple of PrintOut (or
something like that) statements, the whole thing ran fine.
So, if you feel you need your GoSub, it's available, but there are better
ways to construct your code nowadays, that were not available way back
when...

NickHK

"IanC" ...
Nick,

Obviously showing my age with the Gosub!

Your solution works fine and with less code, so I will be grateful for,
and
use, that.

Thanks once again.

Ian

"NickHK" wrote:

Ian,
From description, I'm sure the code does work, but either the cell's
value
or the search value are not what you think at that time.
Put in a few Debug.print statements to check their values before you run
the
..Find.

As for GoSub, it is generally considered a bad construct nowadays, as you
can easily replace with more readable code ; replace it with a function
call
or some branching.

NickHK

"IanC" ...

Nick,

I have found that the code works occasionally, but mostly not. It also
fails to work on both my PCs runiing different Excels. I have a
suspicion
that it relates in some way to the look up, because if I hard code in
the
phrase "_SHARE" it works every time. The sheet in question is
re-calculated
in the line immediately before this code.

Yes, the Gosub could be changed. Does this slow down the code?

Ian

"NickHK" wrote:

Ian,
Your code works for me.

By the way, do you really need GoSub ?
Can you not structure your code to avoid this ?

NickHK



"IanC" wrote in message
...
Thanks. That will do fine.

Is there any reason why Find should not work in this instance?


Ian

"NickHK" wrote:

Ian,
If you are only checking in a single cell, why not use InStr ?
If Instr(Sheets("CHART_SHEET").Range("B63").Value,"_S HARE")0 then

NickHK

"IanC"
...
I am trying to use the Find method in VBA to identify whether a
cell
contains
a phrase (in this case "_SHARE") such that if it does a specific
piece
of
code is run. I am using the following code:

With Sheets("CHART_SHEET").Range("b63")
Set C = .Find("_SHARE", LookIn:=xlValues, LookAt:=xlPart)
End With
If Not C Is Nothing Then GoSub 3000

However, even when cell b63 has "_SHARE" as part of its value,
this
still
returns c as equal to Nothing.

One other point that may be relevant is that the cell b63 is
based
on
a
vlookup function.

Thanks in advance,


Ian