• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find text in cell

I

ICdeadppl

New Member
Hi all

Having syntax problem with the last line, any thoughts would be great ty.

Code: 
  dim Actid as string
  Actid = ActiveCell.Value
  Sheets("OPP").Select
  Range("a:a").Select
 
  Selection.Find(What:=Actid, After:=ActiveCell, LookIn:= _
  xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
  xlNext, MatchCase:=False, SearchFormat:=False).Activate
  ActiveCell.Offset(1, 7).Activate
  ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(" & Actid & ",RC[-7])),TRUE,FALSE)"
 
Hi ICdeadppl

Welcome to the forum.

The following seemed to work for me.

Code: 
Option Explicit
 
Sub testo()
Dim Actid As String
Dim fndRw As Long
 
Actid = [A1]
fndRw = Sheets("OPP").Range("A:A").Find(Actid).Row + 1
Sheets("OPP").Range("H" & fndRw) = "=IF(ISNUMBER(SEARCH(" & Actid & ",RC[-7])),TRUE,FALSE)"
End Sub

Take care

Smallman
 
Thanks for the reply
I copy pasted your macro into a test sheet but I'm getting a runtime error on the below line.

Sheets("OPP").Range("H" & fndRw) = "=IF(ISNUMBER(SEARCH(" & Actid & ",RC[-7])),TRUE,FALSE)"
 
Last edited:
OK so here is what I did. Created a new workbook. Named one of the sheets OPP. On the sheet I was on I put value 5 in A1 and I put 5 in Col A of the OPP sheet. Then I ran the macro. No sign of a run time error to be seen. Here is that file. Naturally if the text can not be found you will get a run time error. In this case you will need to put some error trapping around the code.

Take care

Smallman
 

Attachments

  • Find.xlsm
    14.4 KB · Views: 1
Oks did some testing, when Actid = Dog, it enters the folrmula as =IF(ISNUMBER(SEARCH(Dog,A6)),TRUE,FALSE).

Dog should be in "Dog" format but its not being entered that way, also when I change Actid to a WBS element i.e. 1.2.3.4.5.6 thats where it's throwing the runtime error.
 
Hi ,

Try this statement instead of what is at present in the code :

Sheets("OPP").Range("H" & fndRw) = "=IF(ISNUMBER(SEARCH(""" & Actid & """,RC[-7])),TRUE,FALSE)"

The formula can be shortened to : =ISNUMBER(SEARCH("Dog",A19))

and the statement then becomes :

Sheets("OPP").Range("H" & fndRw) = "=ISNUMBER(SEARCH(""" & Actid & """,RC[-7]))"

Narayan
 
You must log in or register to reply here.
Back
Top