ExcelBanter - Formula throwing up an error

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Formula throwing up an error (https://www.excelbanter.com/excel-programming/436659-formula-throwing-up-error.html)

Formula throwing up an error

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.

Formula throwing up an error

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"","""")"

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

"JoeU2004" wrote:

"Gotroots" wrote:
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","")"

What kind of error are you getting? (Klunk!)

If it is a syntax error, I think you want (untested):

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

In other words, each double-quote that is intended to be a character in the
string must be written as two double-quotes.

That is simply the most obvious error. There might be others, once you fix
that one.

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

"Gotroots" wrote in message
...
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.

.

Formula throwing up an error

"Gotroots" wrote:
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

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 -----

"Gotroots" wrote in message
...
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"","""")"

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

"JoeU2004" wrote:

"Gotroots" wrote:
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","")"

What kind of error are you getting? (Klunk!)

If it is a syntax error, I think you want (untested):

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

In other words, each double-quote that is intended to be a character in
the
string must be written as two double-quotes.

That is simply the most obvious error. There might be others, once you
fix
that one.

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

"Gotroots" wrote in message
...
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.

.

Formula throwing up an error

I am using Excel 2007 SP2 and VBA 6.5

"JoeU2004" wrote:

"Gotroots" wrote:
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

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 -----

"Gotroots" wrote in message
...
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"","""")"

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

"JoeU2004" wrote:

"Gotroots" wrote:
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","")"

What kind of error are you getting? (Klunk!)

If it is a syntax error, I think you want (untested):

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

In other words, each double-quote that is intended to be a character in
the
string must be written as two double-quotes.

That is simply the most obvious error. There might be others, once you
fix
that one.

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

"Gotroots" wrote in message
...
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.

.

.

Formula throwing up an error

PS....

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

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 -----

"JoeU2004" <joeu2004 wrote in message
...
"Gotroots" wrote:
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

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 -----

"Gotroots" wrote in message
...
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"","""")"

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

"JoeU2004" wrote:

"Gotroots" wrote:
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","")"

What kind of error are you getting? (Klunk!)

If it is a syntax error, I think you want (untested):

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

In other words, each double-quote that is intended to be a character in
the
string must be written as two double-quotes.

That is simply the most obvious error. There might be others, once you
fix
that one.

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

"Gotroots" wrote in message
...
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.

.