Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find method
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find method
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find method
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find method
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find method
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Find method
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Method problem in Excel VBA.... | Excel Worksheet Functions | |||
Problem with Find Method | Excel Programming | |||
Problem with the FIND method looking for dates. | Excel Programming | |||
Problem with find method | Excel Programming | |||
range.find method called into a VBA function (problem) | Excel Programming |