Cannot find asterisk signed cells

[HerryMarkowitz]

Hi people,
Open an excel file, give a name first sheet as Sheet1.
Enter "apple" into the A5 cell.
Enter "*" into the A8 cell.
Run this macro;

Please Login or Register  to view this content.

Then run this macro;

Please Login or Register  to view this content.

Why second macro above cannot find asterisk sign ?

[HaHoBe]

Hi, HerryMarkowitz,

the asterisk is used as a wildcard - if you want to search for that character slightly alter the If-Statement:

Please Login or Register  to view this content.

Ciao,
Holger

[Andy Pope]

As the help on Like says

'To match the special characters
' left bracket ([),
' question mark (?),
' number sign (#),
' and asterisk (*),
' enclose them in brackets. The right bracket (]) can't be used within a group to match itself, but it can be used outside a group as an individual character.

Please Login or Register  to view this content.

[HerryMarkowitz]

You are perfect. Thank you.

[HerryMarkowitz]

Hi again,
Set any vlookup function in your Sheet1 cell,
and,
make sure that your vlookup function returns #N/A.

Then, copy #N/A including cell and paste values.

Run following code to find #N/A...

Please Login or Register  to view this content.

When you run the code above, you will see that code goes error...
Any idea ?

[Andy Pope]

because for the cell with the formula these are the returned values when VLOOKUP returns #N/A error

Please Login or Register  to view this content.

[HerryMarkowitz]

Hi Andy Pope,
I am not "immediate window" expert.
Could you please explain with more detail ?

[Andy Pope]

select cell with VLOOKUP formula
goto VBE (ALT+F11)
goto immediate window (CTRL+G)
enter the following command and press Enter

?activecell.Value,activecell.text

The result of Value is not #N/A but Error 2042. This is the reason your Like test false.

You can either text value using ISERROR function. or use .Text property for Like comparison. Becareful if using the .text approach as that property contains what is actually displayed by the cell so can vary.