Filldown stops abruptly

[gootroots]

A command button fires the following code

Range("AV10").Select
Selection.AutoFill Destination:=Range("AV10:AV7800"), Type:=xlFillDefault
Range("AV10:AV7800").Select

When AV2967 is reached the following formula changes from

{=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B2962&"G house")),"G
house","")}

to:

{=#VALUE!}

If I manually fill down the formula past where it changes to {=#VALUE!} the
formula does not revert to {=#VALUE!}

Does anyone know why the formula suddenly should change

[Jacob Skaria]

Is B2962 within the formula an absolute reference or a relative reference

The below format should work when you have a formula in G4.
Range("G4").AutoFill Destination:=Range("G4:G24"), Type:=xlFillDefault

OR without using autofill you can apply the formula to a range as below.
Excel would automatically change the references

Range("G4:G24").Formula = "=SUM(A4:F4)"

If this post helps click Yes
---------------
Jacob Skaria

[gootroots]

Hi Jacob Skaria

B2962 is a relative reference

I tried out your example

Range("G4:G24").Formula = "=SUM(A4:F4)"

and it worked great.

my formula is an array entered formula and I could not replicate the results
I got from your formula

Here is my formula

Range("L10:L7800").Formula = "=IF(SUM(--('A Use'!A$1:A$10000&'A
Use'!C$1:C$10000=B10&'G house')),'G house',"")"

I am getting an debug error when executed.

When i modified it to

Range("L10:L7800").Formula = {"=IF(SUM(--('A Use'!A$1:A$10000&'A
Use'!C$1:C$10000=B10&'G house')),'G house',"")"}

it fill the range L10:L7800 but was not received by the range as a formula,
more like a text entry, strange!

[Jacob Skaria]

Try
Range("L10:L7800").FormulaArray = "=yourformula"

[Gotroots]

I am getting an error, here is the formula I am using

Range("L10:L7800").FormulaArray = "=IF(SUM(--('A Use'!A$1:A$10000&'A

Use'!C$1:C$10000=B10&"G house")),"G house","")"

B10 is a relative reference.

Thank you for helping me out.