Problem with Find method

[Guest]

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

 

[NickHK]

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

NickHK

 

[Guest]

Thanks. That will do fine.

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


Ian

 

[NickHK]

Ian,
Your code works for me.

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

NickHK

 

[Guest]

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]

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

 

[Guest]

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]

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