Error entering an Array Eneter Formula using VBA

[jprlimey]

I'm having difficulty entering an array entered formula using vba.

I wrote the formula in the worksheet first using row/column format as per the following

Please Login or Register  to view this content.

The above formula worked great no issues, I then added the following to my sub routine

Please Login or Register  to view this content.

I get an error stating "Unable to set FormulaArray property of the Range class" it hangs up on the .FormulaArray line

Any ideas as to what I'm doing wrong.

[Bernie Deitrick]

You need to double every double quote within the outside double quotes, so the end is changed from

,"")"
to

,"""")"

The best thing to do with formulas in VBA is to get the formula working, then record a macro where you re-enter the formula.

[jprlimey]

Bernie,

Thanks very much that did the trick, i complpetely forgot about the double quotes.

Now it hangs up on the following line, with "Argument not optional" error

Please Login or Register  to view this content.

Thanks

[jprlimey]

I fixed the autofill using the following

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Again Bernie thanks for your guidance.

[Bernie Deitrick]

Your space is in the wrong place:

.Range("B2").AutoFill .Range("B2:B" & lr)



You could also just copy and paste - change that line to

.Range("B2").Copy Range("B2:B" & lr)