cells.Find not working

[ncduke]

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.

Please Login or Register  to view this content.

[shg]

Whatever Name is, name it something else. That's a reserved word in VBA.

[ncduke]

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

[shg]

Don't do that. Name is a reserved word in the language.

[ncduke]

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

[shg]

Like is assuredly a reserved word -- it's a comparson operator. Look it up in Help.

But there's no need for Like in those examples.

Please Login or Register  to view this content.

[ncduke]

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

[shg]

That's true, you can, but there's no need for it in your examples, and Like is a very heavy operator.

[ncduke]

coming back to my earlier question regarding cells.find not working,

Here is what I am using:

Please Login or Register  to view this content.

Now after first 2 commands, I see the variable Y value in watch window and it gives me the correct value. So I am sure I am in the correct worksheet and so on.

After the cells.find command, the value of Y doesnt change. Ideally it should point to a different cell and give me Y=Description of Jobs Created*

Can you tell me what I am doing wrong ?

Thanks

[shg]

As a matter of practice, you shouldn't follow Find with Activate -- it will error if the sought value is not found.

That said, does it find the value and activate the new cell?

[ncduke]

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 ?

[shg]

Post a sanitized workbook with the code.

Can a macro cells.find work properly and find a cell text which is locked for editing ?

A macro can unprotect the sheet to operate, or protect a sheet with the UserInterfaceOnly option to enable it to operate.

[ncduke]

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

[ncduke]

You might find this interesting:

I have the following code:

Please Login or Register  to view this content.

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

[shg]

Protected worksheet?

[ncduke]

No, I removed the protection from one of them just to be sure.
I also tried Sheets("Prime Recipient").Range("B33").Value = FTE

but nothing works.

[ncduke]

Just wanted to confirm whether
Sheets("Prime Recipient").Range("B33").Value = FTE

is a valid command.

FTE is a variable that gets some value through the earlier part of the code. I can see in the watch window that the code is assigning correct values to this variables.

Essentially just updating the cell with this value is not working.

[shg]

Please Login or Register  to view this content.

...is not the same as

Please Login or Register  to view this content.

Both are valid constructs, though the selecting in the first case is unnecessary. If the sheet is protected and B33 is locked, the first will fail on the Select, and the second will fail on the assignment.

If you want to post a useful amount of code and some context, I'm happy to help. The little fragments are not much use.

[ncduke]

I have unprotected the entire sheet. Besides, even originally, these cells are input cells which means user has to enter text or numbers here.

Here is some useful amount of code :

Please Login or Register  to view this content.

Certain parts of the code pertain to logic in the earlier part of the program and can be overlooked for this discussion e.g.

Please Login or Register  to view this content.

[shg]

Comments appear above the code they refer to.

Please Login or Register  to view this content.

[ncduke]

There are multiple file types and each code could have any file type. So code is appended to the file type. E.g

2010q3-203.xls is one valid file, similarly
2010q3-303s.xls is another valid file.

Moreover, there will always be only 1 file associated with a code. So what I am doing is trying a code with each file type possible. If there is any error I have the

Please Login or Register  to view this content.

given at the start of the program. This answers your first question.

Coming to the second one. Whichever workbook gets opened, I am not sure which worksheet gets activated, I have to ensure that the worksheet "Prime Recipient" is the one that gets activated. It is better to be safe. That was my thought for including :

Please Login or Register  to view this content.

Finally, I am assigning the value of myworkbookname to Y just to use it in the following IF statement. I could do away with it, but I dont see that to be a problem or issue.

Currently I want to focus on the update part. Once that is resolved, I can tidy up the code.

Thanks

[ncduke]

Regarding the IF statement and "first test is subsumed by the second".

Thanks for pointing it out, I will incorporate it.

I think I can just keep the second part.

Please Login or Register  to view this content.

Thanks

[ncduke]

This might be surprising.

I changed the code as follows:

Please Login or Register  to view this content.

Essentially, first I manually edited the EnableSelection option of the worksheet to xlNoRestrictions and checked whether cells.find is working. It did work.

So I thought of putting it in the code as

Please Login or Register  to view this content.

Then things were working the way it should. So I just wanted to confirm whether it was this change itself that caused the program to work properly. Hence I removed all the files, brought in new ones from backup. In these files I had not altered anything manually. Then I commented the

Please Login or Register  to view this content.

.

Even then the program was working and behaving normally. Now my concern is what if the next time, during actual run, the cells.find misbehaves. Any guesses ?

Thanks

[ncduke]

Just to elaborate and emphasize,

after commenting the

Please Login or Register  to view this content.

it appears that I have made no changes and suddenly the cells.find started working.

This is what is not clear to me.

Thanks