| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula throwing up an error | Excel Programming | |||
| Formula throwing up an error | Excel Programming | |||
| Paste Special throwing an error | Excel Discussion (Misc queries) | |||
| aconcat UDF throwing a #NAME error | Excel Worksheet Functions | |||
| Throwing an error ... on purpose! | Excel Programming | |||
Linear Mode
