Formula throwing up an error

  • Thread starter Thread starter Gotroots
  • Start date Start date
G

Gotroots

Can anyone tell me why I should be getting an error.

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


B10 by the way is a relative reference.

Thank you for your time.
 
Thankyou guys for the help

Yes it was a syntax error I was getting

I tested out
Range("L10:L7800").FormulaArray = "=IF(SUM(--('A Use'!A$1:A$10000&'A
Use'!C$1:C$10000=B10&""G house"")),""G house"","""")"
Click to expand...

and it worked, except in each of the cells in the range L10:L7800 the
relative reference B10 did not change, ie. =B11, =B12, =B13 etc
 
Gotroots said:
it worked, except in each of the cells in the range L10:L7800 the
relative reference B10 did not change, ie. =B11, =B12, =B13 etc
Click to expand...

You probably should mention what version of Excel and VBA you are using.
But I confirmed your observation using Excel 2003 SP3 and VBA 6.5.

Bummer! Works as intended for Range(...).Formula, but not for
Range(...).FormulaArray.

I would do the following to work around this "feature" (untested).
Hopefully, someone has a better idea.

Dim i as Long, cell as Range
i = 9
For Each cell in Range("L10:L7800")
i = i + 1
cell.FormulaArray = _
"=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B" _
& i & "&""G house"")),""G house"","""")"
Next Cell


----- original message -----
 
I am using Excel 2007 SP2 and VBA 6.5


JoeU2004 said:
You probably should mention what version of Excel and VBA you are using.
But I confirmed your observation using Excel 2003 SP3 and VBA 6.5.

Bummer! Works as intended for Range(...).Formula, but not for
Range(...).FormulaArray.

I would do the following to work around this "feature" (untested).
Hopefully, someone has a better idea.

Dim i as Long, cell as Range
i = 9
For Each cell in Range("L10:L7800")
i = i + 1
cell.FormulaArray = _
"=IF(SUM(--('A Use'!A$1:A$10000&'A Use'!C$1:C$10000=B" _
& i & "&""G house"")),""G house"","""")"
Next Cell


----- original message -----



.
Click to expand...
 
PS....
Bummer! Works as intended for Range(...).Formula,
but not for Range(...).FormulaArray.
Click to expand...

As I understand (perhaps incorrectly) the intent of your formula, perhaps
the following non-array formula will do the same thing without the messy
for-loop:

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


----- original message -----
 
Back
Top