AutoFilter

[Hyflex]

How do I make my following autofilter compatible with Excel 2010

Please Login or Register  to view this content.

It doesn't do anything in my macro (I've noticed this in a bunch of macros that I have)
Cheers
- Hyflex

[Firefly2012]

Hi

That should work in 2010. If you step thru the code in the VBE using the F8 key does the first statement get executed and the code proceed to the second or does something else happen eg the code errors/ends? Do you have On Error Resume Next applied above this code in the sub?

[Hyflex]

Hmm it seems to be filtering too many rows...

[snb]

Yes, I can see that too in your example file....

[Hyflex]

See attachment

It's supposed to match C and B in both sheets & import like the array shows... (Matched Rows...)

[snb]

probably:

Please Login or Register  to view this content.

[Hyflex]

Wouldn't an array be better because in my main sheet the data I'm playing with is massive!

[snb]

You are the only one who can test that.

[Hyflex]

I'm unsure how to edit this macro to make it import only spesific columns...

As you can see in my example it's importing Column O from Data into Column 16, so you'd think that Column N would be imported to column 15? Nope. I import that to column 16

I've semi-fixed my first macro but it's also not checking date (I've commented that out because that's where the error is)
Anyone know how to fix?

[snb]

I have no idea what you are trying to accomplish nor waht is the relationship between both worksheets.
Please explain in plain English.

[Hyflex]

The relationship is column B and C

If you run the macro in my latest attachment you'll see exactly what it is supposed to be doing but I need it to match the date aswell as the IDnumber.

Match EACH Row in Sheet("RU") in Column B to a Row in Sheet("RA") in Column B
If there is a matched row found, check if the value in column c also a match.
If a match is found we will know both rows where the match is found.
The Array then imports and works out that matched rows in Sheets("RU").Range("H") wants to Import data from Sheets("RA").Range("J")

I've added some colors so you can see the matched cells It has to have both the IDnumber and the date matched to carry on and import

[snb]

Please Login or Register  to view this content.

[tigeravatar]

Hyflex,

Does it have to be a macro? The end result can be accomplished with a formula. Attached is your example file with this formula in cell H2 and then copied over and down to cell M17:
=INDEX(RA!J$1:J$5,SUMPRODUCT((""&RA!$B$2:$B$5&""=""&$B2&"")*(RA!$C$2:$C$5=$C2)*ROW(RA!$J$2:$J$5)))

[Hyflex]

Yeah it kind of does have to be a macro (in an array if possible) the macro in the document works good apart from the second autofilter (date)

If I run the version you did there it goes so so slow in my main sheets >_<

[tigeravatar]

Hyflex,

Give this a try. It uses .Find instead of .AutoFilter:

Please Login or Register  to view this content.

[Hyflex]

I must say tigeravatar you are a genius! It works perfectly and it's easy to edit and it's really really fast.

Thank you very very much! Reputation Added!!!

[tigeravatar]

You're very welcome