Add wildcard to workbook cell searching macro. (ws.cells)

[chris-uk-lad]

This is an enhancement on a previous solution i needed help with.

http://www.excelforum.com/excel-prog...problem-2.html

I have a workbook with a macro that searches through worksheets to find certain values and place the location of the value in a worksheet cell. The following line ontains the found cell.

Please Login or Register  to view this content.

This on the first run contains:

01_Access/aa7 (House Num)

Which has been fine as this is the specified search criteria. The search criteria is the exact same now but i now need to compensate with values that have characters before and after e.g.

AAA [01_Access/aa7 (House Num), 4]

This needs to be done without altering the search criteria. Ive tried putting the wildcard character into the above line and its not working, maybe im using it wrongly im not sure.

Can anyone enlighten me?

[romperstomper]

If your search criteria is '01_Access/aa7 (House Num)' then the code you have will already find cells that contain that value since you are using xlPart, not xlWhole as the LookAt argument.

[Andy Pope]

You are already using the xlpart option on the find so wildcards should not be needed.

But I guess you could add wildcards

Please Login or Register  to view this content.

[chris-uk-lad]

thanks for the quick response. Ive taken the example Phil derived last time and altered to show what i mean. (attatched)

Sheet 1 (Maps) should populate Mapped/Referenced when the value in Mapping Reference is found. This works when the cell on Sheet 4 (S2) contains:

Please Login or Register  to view this content.

However when altered to contain:

Please Login or Register  to view this content.

[/CODE]

It does not find the cell.

[romperstomper]

The problem is not with the Find (which locates the data just fine) but with your Like comparison, which needs to be adjusted to:

Please Login or Register  to view this content.

[chris-uk-lad]

that worked fine, thank you

just a final quesiton, related to the worksheet not the task,

how would i specify as an output, a certain cell within say, Sheet3, e.g A:2?

So like:

Please Login or Register  to view this content.

(i know the above doesnt work, just an idea of what id like to do)

[Andy Pope]

either of these

Please Login or Register  to view this content.

[romperstomper]

Please Login or Register  to view this content.

with either of Andy's suggested syntaxes.

[Andy Pope]

thanks. I missed that missing dot.

[chris-uk-lad]

sorry i didnt specify better, ws.cell seems to only call the first sheet (Sheet1), i actually want Sheet3 specified.

[Andy Pope]

I assumed ws was an object you had declared that referenced the appropriate sheet.

If not then

Please Login or Register  to view this content.

Or what ever the code name of the sheet is.

[chris-uk-lad]

Thats worked fine, thanks everyone for your help.

[chris-uk-lad]

Apologies, premmature solving.

That works fine, when the sheet isnt named XD

So instead of using Sheet3 / Sheet4 etc. how can i use it based on the sheets name instead of corresponding number? e.g worksheet "Housing"

Debug.Print Housing.Range("D3").Value

this doesnt work, requires an object :x

[romperstomper]

You would use:

Please Login or Register  to view this content.

Andy's code was using codenames - usually more reliable as they do not change when you alter the tab name.