long formula to vba macro - error 1004 unable to set formulaarray property

This is a follow up question to my question here:

http://www.excelforum.com/excel-prog...-a-column.html

XOR LX suggested a formula to help with the issue, I then took that formula and recorded a macro to make it work in a macro and display the values in Column I. But the formula was longer than 255 characters in length so I followed the trick described here
http://dailydoseofexcel.com/archives...rmulas-in-vba/

But I am still getting the Unable to set the FormulaArray Error - RunTime Error 1004.

Can someone please look at my code and help me figure out the issue? I will really appreciate any help or pointers. This is the code I have so far.

Please Login or Register  to view this content.

How you have split up your formula is not correct, the string "first" ends in a comma, it needs to work as a complete formula with a function such as first() replace the second part.

Thanks yudlugar.
I have been banging my head trying to split the formula so that it makes sense :-( but have been unsuccessful so far !

Hi,

I had a go at doing this, I don't think XOR LX's formula is correct. I get a "formula contains an error message" when I try to enter it.

I posted a macro solution in your other thread.