Formula Array not adjusting to range

[LightHeart7]

Please Login or Register  to view this content.

I have this code and its working great but only on the first line. I need on B4 the last part to be

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

on B5 and so forth. Is there a way to get it to do this?

[XOR LX]

Hi,

Perhaps you'd be kind enough to give some feedback on some of your other (unfinished) threads before starting a new one? Especially since the formula in this post seems to be almost identical to the one in the other, and also considering I put a not insignificant amount of time into trying to help you in that other post.

http://www.excelforum.com/excel-form...ank-cells.html

Regards

[LightHeart7]

I know formulas will work down the range so how can I get this one to? Its important that I'm getting it got come down A1 to A2 to A3 etc.

[LightHeart7]

This has to be an array. I've seen brief mentions of this.. R1C1? I'm not sure. I couldn't just use this in a regular cell because overall i'm going to have so many its too much for the cell itself. maybe another method to get through this?

[Izandol]

You must fill:

Please Login or Register  to view this content.

[LightHeart7]

I'm getting an error unfortunately. "Unable to set the FormulaArray property of the Range Class". Did I miss something? I know I have it under a macro type.

[ragulduy]

.formulaarray has to be r1c1 references.

Please Login or Register  to view this content.

I'm not 100% you can use the workaround in the second line without looping, either loop through each cell or re-write your formula with r1c1 references and assign to the whole ranges formulaarray property.

[Izandol]

Which version are you using - I have tested in 2013 without problem.

[Izandol]

FormulaArray does not require R1C1 references.

[ragulduy]

@Izandol,

It does in 2003... From the help file:
FormulaArray Property
See AlsoApplies ToExampleSpecificsReturns or sets the array formula of a range. Returns (or can be set to) a single formula or a Visual Basic array. If the specified range doesn't contain an array formula, this property returns Null. Read/write Variant.

Remarks
If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style (see the second example).

Example
This example enters the number 3 as an array constant in cells A1:C5 on Sheet1.

Worksheets("Sheet1").Range("A1:C5").FormulaArray = "=3"

This example enters the array formula =SUM(R1C1:R3C3) in cells E1:E3 on Sheet1.

Worksheets("Sheet1").Range("E1:E3").FormulaArray = _
"=Sum(R1C1:R3C3)"

Edit: checked msdn for later versions and seems the same?

[LightHeart7]

Oh, I should remember to mention which version. For this one I'm using 2013. For more information this is what is calling the Macro

Please Login or Register  to view this content.

I'm still getting that error though. Perhaps i'm doing something off. Again I'm using just

Please Login or Register  to view this content.

[Izandol]

The Help file is incorrect in this regard.

[LightHeart7]

I know this isn't the easiest thing to work with. I can't thank you guys enough though for trying to work with me on this!

[ragulduy]

@Izandol,

I'd be interested on your thoughts on this thread:
http://www.excelforum.com/excel-prog...la-in-vba.html

It's one that stuck out in my mind that the r1c1 thing seemed to fix...

[Izandol]

Can you provide a workbook? The code works in my copy of 2013.

[ragulduy]

Just about to leave for the weekend, will try and remember on monday.

[Izandol]

@yudlugar,

I think my interpretation of that thread is different from yours; it seems the formula string was simply too long because the external:=true parameter of the Address property was including the workbook name as well as the worksheet name and both FormulaArray and Evaluate require a formula string below 255 characters.

I do not think it will take you long to test this.

[LightHeart7]

I'm getting another error this time. Error: -2147417848(80010108) method 'formulaarray' of object 'range' failed

[Izandol]

I think you must provide a workbook. The code does work here.

[LightHeart7]

Is.. it possible its doing this because it finds 2 and then the rest are "#num!"?

[Izandol]

I do not think so because my testing was performed in a blank workbook and so each formula returned #NUM.

[LightHeart7]

Here is a mock up. Its a bit odd, its seems to be freezing up a bit or having glitchs.

[Izandol]

I believe that workbook may be corrupt because it behaves very strangely.

[LightHeart7]

If you go directly into the codes and delete them, it doesn't act all weird. it was something in the code set up maybe?.. I've had this on and off when playing with the code and that was a brand new workbook.

[LightHeart7]

If you're wondering, I will be putting another formula for C4:C8 in the next one over but instead of the original A:A on on the first column in the formula it will be B:B

[Izandol]

It is my mistake - you must disable events when autofilling:

Please Login or Register  to view this content.

[LightHeart7]

Oh! Well i didn't divulge everything. Oooh Its working! Thank you thank you!!! This has been such a pain in my side. I'm so happy!