You all caught the problem with this solution (which I still like). I need to find a String within a String, not an exact String match. But I think I found where I went wrong. The InStr command is very picky about what parms you use. Thus when I changed this to InStr(1, Cell.Value, ChkFor, vbTextCompare) the function worked.
Greg Della-Croce Skype: gdellacroce55 Cell: 407-408-2572 greg_della-cr...@sil.org "If you assume you know the answer, you will miss having a breakthrough. It’s okay to do what you did yesterday, but it will never be amazing again.". - *Rod Favarod, CEO of Spredfast* On Fri, Jan 27, 2017 at 4:43 AM, martinez.david533 via MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> wrote: > > -------------------------------------------- > On Fri, 1/27/17, georgemartin812 via MS EXCEL AND VBA MACROS < > excel-macros@googlegroups.com> wrote: > > Subject: Re: $$Excel-Macros$$ Re: Simple function but it returns #VALUE > To: excel-macros@googlegroups.com > Date: Friday, January 27, 2017, 9:50 AM > > > -------------------------------------------- > On Thu, 1/26/17, libertystringer via MS EXCEL AND VBA MACROS > <excel-macros@googlegroups.com> > wrote: > > Subject: Re: $$Excel-Macros$$ Re: Simple function but it > returns #VALUE > To: excel-macros@googlegroups.com > Date: Thursday, January 26, 2017, 10:47 PM > > > -------------------------------------------- > On Thu, 1/26/17, julienneschindler via MS EXCEL AND VBA > MACROS <excel-macros@googlegroups.com> > wrote: > > Subject: Re: $$Excel-Macros$$ Re: Simple function but > it > returns #VALUE > To: excel-macros@googlegroups.com > Date: Thursday, January 26, 2017, 9:29 PM > > > -------------------------------------------- > On Thu, 1/26/17, Vicky <jorwalvi...@gmail.com> > wrote: > > Subject: Re: $$Excel-Macros$$ Re: Simple function but > it > returns #VALUE > To: excel-macros@googlegroups.com > Date: Thursday, January 26, 2017, 6:53 PM > > Hi Greg, > The given code only works when you are comparing > the contents of the entire cell from one in the > range. It > won't work if your chkFor cell is matching partially > with one of the cells from range. > Regards,Vicky > On 26-Jan-2017 9:47 > PM, "Greg Della-Croce" <greg_della-cr...@sil.org> > wrote: > Vicky, thanks for the idea. I will see how it > works for me. I appreciate the time/effort you put > in to > help me! > Greg > > Greg > Della-CroceSkype: > gdellacroce55Cell: 407-408-2572greg_della-cr...@sil.org"If > you assume you know the > answer, you will miss having a breakthrough. It’s > okay > to > do what you did yesterday, but it will never be > amazing > again.". - Rod Favarod, CEO of > Spredfast > > > > > > On Thu, Jan 26, 2017 > at 11:13 AM, Vicky <jorwalvi...@gmail.com> > wrote: > Hi Greg, > I have found a solution. Hope this > helps! > > Function > Contains(ChkFor As String, ChkRng As > Range) > For > Each cell In ChkRng If cell.Value = ChkFor > Then > Contains = "Found" Exit > For > Else Contains = "Not > Found" End If > Next > cellEnd Function > The above code worked for me. :) > Regards,Vicky > On Thursday, January 19, 2017 at 7:15:03 PM UTC+5:30, > Greg > Della-Croce wrote:I am trying to > create a very simple function that will take a string > of > text and read a range of texts to see if I have any > that > string in any of the ranges of the texts. Should > be a > 10-minute problem to solve. I am however getting > a > #VALUE > back.I call the function with this:=Contains(F2,' > Scrape'!$D$2:$D$2062) > > The function is this: Function Contains(ChkFor As > String, > ChkRng > As Range) As String > For Each cell In > ChkRng If > (InStr(cell.Value, ChkFor, 1)) Then > Contains = > "Found" > Exit For > End If > Contains = "Not Found"Next cellEnd Function > > OK, I said it was simple :) However, can anyone > point to > the problem in this code. I have looked at it > until I > have become frustrated. > Thanks, Greg > > > > -- > > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And > do you > wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official > Facebook page of this forum @ https://www.facebook.com/discu > ssexcel > > > > FORUM RULES > > > > 1) Use concise, accurate thread titles. Poor thread > titles, > like Please Help, Urgent, Need Help, Formula Problem, > Code > Problem, and Need Advice will not get quick attention > or > may > not be answered. > > 2) Don't post a question in the thread of another > member. > > 3) Don't post questions regarding breaking or > bypassing > any security measure. > > 4) Acknowledge the responses you receive, good or > bad. > > 5) Jobs posting is not allowed. > > 6) Sharing copyrighted material and their links is > not > allowed. > > > > NOTE : Don't ever post confidential data in a > workbook. > Forum owners and members are not responsible for any > loss. > > --- > > You received this message because you are subscribed > to a > topic in the Google Groups "MS EXCEL AND VBA > MACROS" group. > > To unsubscribe from this topic, visit https://groups.google.com/d/to > pic/excel-macros/4lNWf251gdg/ unsubscribe. > > To unsubscribe from this group and all its topics, > send an > email to excel-macros+unsubscribe@googl > egroups.com. > > To post to this group, send email to excel-macros@googlegroups.com. > > Visit this group at https://groups.google.com/grou > p/excel-macros. > > For more options, visit https://groups.google.com/d/op > tout. > > > > > > > -- > > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And > do you > wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official > Facebook page of this forum @ https://www.facebook.com/ > discussexcel > > > > FORUM RULES > > > > 1) Use concise, accurate thread titles. Poor thread > titles, > like Please Help, Urgent, Need Help, Formula Problem, > Code > Problem, and Need Advice will not get quick attention > or > may > not be answered. > > 2) Don't post a question in the thread of another > member. > > 3) Don't post questions regarding breaking or > bypassing > any security measure. > > 4) Acknowledge the responses you receive, good or > bad. > > 5) Jobs posting is not allowed. > > 6) Sharing copyrighted material and their links is > not > allowed. > > > > NOTE : Don't ever post confidential data in a > workbook. > Forum owners and members are not responsible for any > loss. > > --- > > You received this message because you are subscribed > to a > topic in the Google Groups "MS EXCEL AND VBA > MACROS" group. > > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/excel-macros/ 4lNWf251gdg/unsubscribe. > > To unsubscribe from this group and all its topics, > send an > email to excel-macros+unsubscribe@ > googlegroups.com. > > To post to this group, send email to excel-macros@googlegroups.com. > > Visit this group at https://groups.google.com/ > group/excel-macros. > > For more options, visit https://groups.google.com/d/ > optout. > > > > > > -- > > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And > do you > wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official > Facebook page of this forum @ https://www.facebook.com/discussexcel > > > > FORUM RULES > > > > 1) Use concise, accurate thread titles. Poor thread > titles, > like Please Help, Urgent, Need Help, Formula Problem, > Code > Problem, and Need Advice will not get quick attention > or > may > not be answered. > > 2) Don't post a question in the thread of another > member. > > 3) Don't post questions regarding breaking or > bypassing > any security measure. > > 4) Acknowledge the responses you receive, good or > bad. > > 5) Jobs posting is not allowed. > > 6) Sharing copyrighted material and their links is > not > allowed. > > > > NOTE : Don't ever post confidential data in a > workbook. > Forum owners and members are not responsible for any > loss. > > --- > > You received this message because you are subscribed > to > the > Google Groups "MS EXCEL AND VBA MACROS" group. > > To unsubscribe from this group and stop receiving > emails > from it, send an email to excel-macros+unsubscr...@googlegroups.com. > > To post to this group, send email to excel-macros@googlegroups.com. > > Visit this group at > https://groups.google.com/group/excel-macros. > > For more options, visit > https://groups.google.com/d/optout. > uterea politica era gestionata de marii proprietari > industriali liberali si > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And > do you > wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official > Facebook page of this forum @ https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread > titles, > like Please Help, Urgent, Need Help, Formula Problem, > Code > Problem, and Need Advice will not get quick attention > or > may > not be answered. > 2) Don't post a question in the thread of another > member. > 3) Don't post questions regarding breaking or > bypassing > any > security measure. > 4) Acknowledge the responses you receive, good or > bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is > not > allowed. > > NOTE : Don't ever post confidential data in a > workbook. Forum owners and members are not > responsible for > any loss. > --- > You received this message because you are subscribed > to > the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving > emails > from it, send an email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at > https://groups.google.com/group/excel-macros. > For more options, visit > https://groups.google.com/d/optout.omiccel > mai important in agricultura marii > proprietari devin stapani asupra > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you > wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official > Facebook page of this forum @ https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread > titles, > like Please Help, Urgent, Need Help, Formula Problem, Code > Problem, and Need Advice will not get quick attention or > may > not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing > any > security measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not > allowed. > > NOTE : Don't ever post confidential data in a > workbook. Forum owners and members are not responsible for > any loss. > --- > You received this message because you are subscribed to > the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails > from it, send an email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at > https://groups.google.com/group/excel-macros. > For more options, visit > https://groups.google.com/d/optout.oarta > tindea tot mai mult sa considere Romania alaturi > de alte zone dinPeninsula Balcanica provincie > turceasca. in august 1876 premierul roman l-a salutat la > Sibiu pe Franz osifsi l-a asigurat de amicitia > tarii noastre. Cum un conflict ruso-otoman putea avea ca > teatru de desfasurare spatiul romanesc I.C. Bratianu > s-a orientat catre tratative directe cu Rusia perfect > interesata si ea de un acord cu Romania in > timp ce tabara neutra isti or desfasura in tara si > strainatate o puternica propaganda prin intermediul > unor politicieni ce agitau spectrul pericolului rusesc. > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you > wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official > Facebook page of this forum @ https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, > like Please Help, Urgent, Need Help, Formula Problem, Code > Problem, and Need Advice will not get quick attention or may > not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any > security measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not > allowed. > > NOTE : Don't ever post confidential data in a > workbook. Forum owners and members are not responsible for > any loss. > --- > You received this message because you are subscribed to the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails > from it, send an email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at > https://groups.google.com/group/excel-macros. > For more options, visit > https://groups.google.com/d/optout.area guvernare conservatoare > 1871-1876 . Prezidata de Lascar Catarg u trebuie raportata totodata si > la ceea ce s-a realizat in timpul ei: dezvoltarea proprietatii funciare > construirea creditului rural si a celui urban intemeierea societatilor de > asigurare extinderea retelei nationale de comunicatii consolidarea > monarhiei constitutionale actiunea diplomatica pentru proclamarea > independentei de stat in 1873 lupta - tot pe cale diplomatica desfasurata > -pentru dobandirea dreptului de a incheia tratate separat de Poarta > manifestarea independentei economice prin conventii comerciale si de > navigatie incheiate cu Austro-Ungaria si Rusia la jumatatea deceniului opt > al secolului al XIX-lea. Evident nu lipsesc aspectele contradictorii cum > sunt nemultumirile starnite de unele modificari aduse legii tocmelilor > agricole de politica liberului schimb in detrimentul protectionismuiui > economic sau de unele initiative privind restructurarea regimului politic. > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to a topic in the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/excel-macros/4lNWf251gdg/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at https://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
