Thread: cells.Find not working

Hi,

I have a particular text to search for ("Description of Jobs Created") in a worksheet.
I am sure this string is present but on debugging I can see that cells.find is not working.

I have 3 checks out of which the first and last is going to the correct cell positions however, the check after cells.find is not searching and going to the cell. Please help.

name.Sheets("Prime Recipient").Select
                name.Sheets("Prime Recipient").Activate
                ActiveSheet.Range("B4").Activate
                Y = ActiveCell.Value  ' Check 1
                               
                lookstrg = "Description of Jobs Created*"
                Cells.Find(What:=lookstrg, LookIn:=xlValues, MatchCase:=False).Activate
                
                Y = ActiveCell.Value  ' Check 2
                
                ActiveSheet.Range("B33").Select
                Y = ActiveCell.Value   ' Check 3

Before the first sentence of the code, I have
Set name = Workbooks.Open(Filename:=Directory & "\2010q3-" & code & "sContr.xls")

I have done that. Another issue that I am facing is that of a LIKE operator.

I have a string variable and I want to use a statement as follows:

If Y like ("*abc.xls") Or ("def.xls") Then

However, I think the like is also a reserved word that means something different in macro.

How to perform the above task (LIKE operator) ?

Thanks

Can I use wildcard characters in your formula

=if lcase(right(myString, 7)) = "abc.xls" or lcase(right(myString, 7)) = "def.xls" then

Instead of

="abc.xls"

can I use

="*abc.xls"

so that tomabc.xls and samabc.xls both are covered

Thanks for giving me the recommendation. I will keep it in mind.

But it doesn't activate the correct cell.

My plan is to find the cell containing the text => "Description of Jobs Created*"
Go to the cell underneath this text.
Update the cell with a variable value that I have calculated earlier in the code.

Currently I have a work around where the cells are hard coded and although I know that this is not a good practice, in this case it wont hurt because the template wont change.

I am preparing for the eventuality where the template does change and then the program would still run had I used the cells.find function. Hope this explains.

Additional question => One of the worksheets is acting strange. I bet this one is protected differently. Certain columns are uneditable. Others are where I have to input stuff. Even the Ctrl +F for regular find doesnt work. It is only editable in certain places where we are expected to enter values. Can a macro cells.find work properly and find a cell text which is locked for editing ?

Due to work security limitations I cant upload the worksheet !

Did you figure out my error in the cells.find statement that I had asked earlier ?

Thanks

You might find this interesting:

I have the following code:

ActiveSheet.Range("B33").Select
                            ActiveCell.Value = FTE
                            
                            ActiveSheet.Range("D33").Select
                            ActiveCell.Value = JobString
                            ActiveWorkbook.Close savechanges:=True

Now what happens is that the macro correctly find the cell B33 and selects and updates it with the value of the FTE variable.

However, after doing that, it is unable to go and select the cell D33. Instead it updates the same cell B33 with the value of the JobString variable.

Furthermore, for some instances, it selects a totally different cell (not even B33) and starts updating it.

Is there a known issue or a problem with my code. This is excel 2003.

Thanks