when i put an Array formula through VBA, it does not work as epected

[galvinpaddy]

Afternoon Happy Campers,

I have a sheet, containing two tabs.

tab 1 - In cell A1, i type a Post Code.

tab 2 - In cell A1 down i have a list of post codes
In Cell B1 down i have a list of addresses
(The range of A:B in tab 2 has been named as DATA)

So, i have a formula that will do it that i can leave in the cells:

Please Login or Register  to view this content.

Which works great when it is entered as an array formula in set cells (B1:B10) but the refresh time for it is slow when the data in cell A1 changes, so i played with the idea of using VBA, and only calling the code via command button.
After some research i came up with the following:

Please Login or Register  to view this content.

But when the above runs, its almost like it executes it too fast and just pastes values without the formula actually refreshing and showing the correct results.

Would anyoen be kind enough to have a go at setting it right for me?

Kind regards
galvinpaddy

[ragulduy]

I think you need to use .formulaarray for vba otherwise it won't be an array formula. So:

Please Login or Register  to view this content.

[galvinpaddy]

Hi, firstly, thanks for your response!
However, i get a similar result.

When i run the macro, it doesnt populate properly for me.
In Tab1 Cell A1, if i type in the post code XXD12 0ZZA
the the array formula should show me 3 different results in column B, but it shows me the same one.

I cant attache the sheet as its over 9kb and i also cant reduce the size as i dont know how to seperate the personal.xls bits (have lots of macros saved there.

But essentially, i have the following in on tab 2.

XX11 2AL Tersus Group
XX8 1AU MOBILE FUN LIMITED
XX8 1AU Mobile Fun Limited
XX8 1AU Pyramid Display Materials Ltd
XXD12 0ZZA Styrene Packaging | Insulation
XXD12 0ZZA RP & P Management
XXD12 0ZZA RICHARDSON BUSINESS EQUIPMENT
XXD7 1HZZ Belgravium (D/A)
XXD7 1HZZ MAINDEC COMPUTER SOLUTIONS
XXD7 1HZZ COMODO CA LTD
XXD7 1HZZ Maindec Computer Solutions
XXD7 1HZZ UK Resource Centre for Women in S E T
XXD7 1HZZ O H S Ltd
XXD7 1HZZ Vantage Trading Ltd

Regards

[JosephP]

you cannot assign the formulaarray to all the cells in one pass-that's a totally different formula; perhaps

Please Login or Register  to view this content.

[galvinpaddy]

Hi JosephP,

When i run your code, i get a compile error, Syntax error.
It highlights the following line in red

Please Login or Register  to view this content.

[millz]

But when the above runs, its almost like it executes it too fast and just pastes values without the formula actually refreshing and showing the correct results.

If it's because it didn't calculate before it's pasted, maybe try adding a ActiveSheet.Calculate before the copy?

[JosephP]

I apologize-typo

Please Login or Register  to view this content.

oughta be

Please Login or Register  to view this content.

[galvinpaddy]

Great stuff, thanks alot to all who offered support, and thank-you to JosephP/Yudlugar for the solution, REP all round :D

**edit - cant add rep for Joseph, apparently i need to spread the rep before adding more for you :D

[JosephP]

don't worry-thank you is sufficient ;-)