Convert .find code to .find like

[Groovicles]

Hi everyone,

I have a number of lines of .find code in my macro like the following:

Please Login or Register  to view this content.

Sometimes the wbNew.Worksheets(1).range("A3").Value does not exactly match the sheet I'm looking in.

How can I convert this code to look for values similar to what's in A3 rather than looking for an exact match?

Any ideas?

Thanks!!

[CK76]

Try concatenating with wild card operator (i.e. "*") at front and back of string.

If I understood your requirement right...

Please Login or Register  to view this content.

[sintek]

Give me an example of what the similar matches could be please

[Groovicles]

Hi CK76

I tried wildcards on the outside of the entire string but error 91, object variable or with block variable not set. Here's how I put it:

Please Login or Register  to view this content.

Any ideas?

[Groovicles]

Hi sintek,

The string could be in the beginning, middle or end of the lookup range. It could have symbols like & as well

[CK76]

Can you upload sample workbook? Worked fine on my end.

[Norie]

If you are using Find you can specify to look at the whole or part of cell using the LookAt at parameter.

[Groovicles]

hi Norie,

I tried your suggestion but got a script out of range error message. Here's how the line looks:

Please Login or Register  to view this content.

[Norie]

Adding lookat:=xlPart would not cause a 'subscript out of range' error.

Is the value in rngC definitely the name of a sheet in Workbooks(2)?

[Groovicles]

Hi Norie,

Yes, the value is definitely in the worksheet name string. For example, rngC could be something like AB Stuff, but in workbooks(2) the sheet name would be ABStuff. Or, rngC would be DCF but in workbooks(2) the sheet name would be DCF Mgmt.

[Norie]

You have to use the exact worksheet name to refer to a worksheet by name, you can't use a partial match, wildcards, like, whatever...

[Groovicles]

Ah, that'll do it then. Thx, Norie.