VBA - Match specific range values back to spreadsheet

[Nightlytic]

Hi,

Could someone take a look at attached for me? I need a macro that will take a specifc range (the range varies, can start anywhere and be a single cell or several rows long, always single column though), lets call it "X", within column K, and match each value in it back to the dataset to the left, leaving "1" in column E corresponding to the relevant reference (ignore all else).

As part of the wider picture, this will leave a string of 0 and 1 values in the data indicating what references users have selected, and will assist some formulae and conditional formatting. I wanted to do the match in VBA because users will sometimes add a few references to a pre-existing large list of references (above the range "X", that's why range "X" varies), and match back to also large datasets which would slow this down a lot... I have the benefit that a previous macro already declares the newly added data as a range, "X", but I don't know how to take each of the values in it and compare back to the dataset.

 

[Chihiro]

Why VBA? You can just use dynamic named range and formula.

Dynamic named range "SavedData"
=OFFSET(Sheet1!$K$3,,,COUNTA(Sheet1!$K:$K)-1)

Then formula for 1, 0 check.
=--ISNUMBER(MATCH(D3,$K$3:$K$9,0))

You can nest it in IF function when Reference is blank.

If you want to do it in VBA, you can use one of many methods to accomplish this.

1. Application.Match()
2. EVALUATE
3. Nested Loop
4. "Scripting.Dictionary"'s dictionary.Exists method.
etc.

 

[Nightlytic]

Hi Chihiro,

I suppose I was very adamant about only matching the new values, for performance sake. You're right though! I could name the range with VBA and let formulas do the rest. Thanks!

Can I sneakily ask, just on =IF(D3="","",--ISNUMBER(MATCH(D3,$K$3:$K$9,0)))

--?

I see it turned the TRUE, FALSE to 1 and 0, does this little trick have a name? Other applications? Amazing.

Kind Regards,
Nightlytic

 

[Chihiro]

I see it turned the TRUE, FALSE to 1 and 0, does this little trick have a name?

Not really. Basically it's using - to coerce TRUE/FALSE into number and then - again to switch back the sign.

Same can be accomplished by *1, or +0.

This is basically equivalent of using N() function.
https://support.office.com/en-us/article/N-function-a624cad1-3635-4208-b54a-29733d1278c9

 

[Nightlytic]

Not really. Basically it's using - to coerce TRUE/FALSE into number and then - again to switch back the sign.

Same can be accomplished by *1, or +0.

This is basically equivalent of using N() function.
https://support.office.com/en-us/article/N-function-a624cad1-3635-4208-b54a-29733d1278c9

Neat, to be honest I didn't know you can change T/F to a number, I normally got flags of 1 and 0 using IF statements... Well, those days are past now

Thank you Chihiro! I appreciate the lesson.

 

[p45cal]

does this little trick have a name?

Double unary.

 

[shrivallabha]

Not really. Basically it's using - to coerce TRUE/FALSE into number and then - again to switch back the sign.

Same can be accomplished by *1, or +0.

This is basically equivalent of using N() function.
https://support.office.com/en-us/article/N-function-a624cad1-3635-4208-b54a-29733d1278c9

Double unary has some interesting story associated with it.
https://teylyn.com/articles/sumproduct_volatile_bug/

Applies to Excel 2007 only per the article.

 

[Chihiro]

Learn something new every day.
I've been using it forever and had never known it's called double unary.