VBA - Quick tweak to an old solved&closed thread

[guiablo]

Hello all,

I was looking for some help to tweak the code posted by Trebor76 several years ago in this thread:

VBA to copy row to another worksheet if cell value is found in a list

I have 3 worksheets in a workbook: "Source" - contains a 2-column list, first column are numbers (integer) and are all unique (i.e. no 2 rows with the same value), and 2nd column is a name (irrelevant in this exercise) "Data" - contains a table with multiple columns, first column is a number...

www.mrexcel.com

It is very close to what I am trying to accomplish now but there is something going on on line 21?
and I get a runtime error 13 type mismatch, which I imagine is because Trebor76's code is specifically looking for numeric values, whereas in search&match that I have, my table also contains non-numeric values (I guess I can call it strings).

A quick read of the thread above plus a screenshot of what I am trying to do, maybe helps the readers understand the task:

Any help would be appreciated!

Cheers,
G

 

[Micron]

Hopefully you won't mind if I take a stab at it without following the link (at least not at this time).
If you have mixed data types, declare the variable(s) as Variant - then it will accept virtually anything, including Null. If you need to do computations on variants, sometimes you have to bring conversion functions into play. It's safer than relying on M$ Office programs to correctly interpret your intent. Unfortunately, Excel seems to lack a built in function for handling null (Access does) so you'd have to create your own.

 

[Alex Blakenburg]

You don't say what line 21 actually is.
Assuming it is the If-Countif line, try dropping the CLng conversion and make it just:

        If Application.WorksheetFunction.CountIf(wsSrc.Range("A:A"), wsData.Range("A" & i)) > 0 Then

 

[guiablo]

Hello,
thanks for the replies, I tried both suggestions,
Micron, your suggestion gives back the same error 'type mismatch'; the only variables are row counts, which seems unrelated to the error;
Alex, your suggestion works perfectly!

Micron, if I try Alex suggestion and set the variables to Variant, it works too,

Cheers to both

 

[Alex Blakenburg]

Thank you for providing feedback on both our suggestions. Glad we could help.