Error, Unable to set the formulaArray property of the range class

up vote
4
down vote

favorite

I've created the following formula:

=IFNA(LOOKUP(10^99,--MID(O2,MIN(IF((--ISNUMBER(--MID(O2,ROW($1:$25),1))=0)*ISNUMBER(--MID(O2,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25))),SUMPRODUCT(MID(0&RIGHT(N2,4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(N2,4),ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

It looks at column "N" and bring through just the number string. If there is an N/A it will then do the same on column "O" which is ran as an array and it works fine.

Yet when I converted it to VBA code, I get the error mentioned in the title .

Range("L2").Select

    Selection.FormulaArray = _

        "=IFNA(LOOKUP(10^99,--MID(RC[3],MIN(IF((--ISNUMBER(--MID(RC[3],ROW(R1:R25),1))=0)*ISNUMBER(--MID(RC[3],ROW(R2:R26),1)),ROW(R2:R26))),ROW(R1:R25))),SUMPRODUCT(MID(0&RIGHT(RC[2],4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(RC[2],4),ROW(R1:R25),1))* ROW(R1:R25),0),ROW(R1:R25))+1,1)*10^ROW(R1:R25)/10))"

What it going wrong?

edited Jul 9 at 18:41

Community♦

asked May 7 '15 at 15:20

Phairplay

662211

.FormulaArray will only accept a 255 character or fewer string. Yours is too long.
– Rory
May 7 '15 at 15:25

what can I use in stead?
– Phairplay
May 7 '15 at 15:29

Can you show some sample data? It may be possible to shorten the formula.
– Rory
May 7 '15 at 15:46

You can put place holders in your formula to get around the limitation. As described here dailydoseofexcel.com/archives/2005/01/10/…
– Dick Kusleika
May 7 '15 at 15:52

give some screenshot of the data
– AHC
May 7 '15 at 21:40

add a comment |

up vote
4
down vote

favorite

I've created the following formula:

=IFNA(LOOKUP(10^99,--MID(O2,MIN(IF((--ISNUMBER(--MID(O2,ROW($1:$25),1))=0)*ISNUMBER(--MID(O2,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25))),SUMPRODUCT(MID(0&RIGHT(N2,4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(N2,4),ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

It looks at column "N" and bring through just the number string. If there is an N/A it will then do the same on column "O" which is ran as an array and it works fine.

Yet when I converted it to VBA code, I get the error mentioned in the title .

Range("L2").Select

    Selection.FormulaArray = _

        "=IFNA(LOOKUP(10^99,--MID(RC[3],MIN(IF((--ISNUMBER(--MID(RC[3],ROW(R1:R25),1))=0)*ISNUMBER(--MID(RC[3],ROW(R2:R26),1)),ROW(R2:R26))),ROW(R1:R25))),SUMPRODUCT(MID(0&RIGHT(RC[2],4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(RC[2],4),ROW(R1:R25),1))* ROW(R1:R25),0),ROW(R1:R25))+1,1)*10^ROW(R1:R25)/10))"

What it going wrong?

edited Jul 9 at 18:41

Community♦

asked May 7 '15 at 15:20

Phairplay

662211

.FormulaArray will only accept a 255 character or fewer string. Yours is too long.
– Rory
May 7 '15 at 15:25

what can I use in stead?
– Phairplay
May 7 '15 at 15:29

Can you show some sample data? It may be possible to shorten the formula.
– Rory
May 7 '15 at 15:46

You can put place holders in your formula to get around the limitation. As described here dailydoseofexcel.com/archives/2005/01/10/…
– Dick Kusleika
May 7 '15 at 15:52

give some screenshot of the data
– AHC
May 7 '15 at 21:40

add a comment |

up vote
4
down vote

favorite

I've created the following formula:

=IFNA(LOOKUP(10^99,--MID(O2,MIN(IF((--ISNUMBER(--MID(O2,ROW($1:$25),1))=0)*ISNUMBER(--MID(O2,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25))),SUMPRODUCT(MID(0&RIGHT(N2,4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(N2,4),ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

It looks at column "N" and bring through just the number string. If there is an N/A it will then do the same on column "O" which is ran as an array and it works fine.

Yet when I converted it to VBA code, I get the error mentioned in the title .

Range("L2").Select

    Selection.FormulaArray = _

        "=IFNA(LOOKUP(10^99,--MID(RC[3],MIN(IF((--ISNUMBER(--MID(RC[3],ROW(R1:R25),1))=0)*ISNUMBER(--MID(RC[3],ROW(R2:R26),1)),ROW(R2:R26))),ROW(R1:R25))),SUMPRODUCT(MID(0&RIGHT(RC[2],4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(RC[2],4),ROW(R1:R25),1))* ROW(R1:R25),0),ROW(R1:R25))+1,1)*10^ROW(R1:R25)/10))"

What it going wrong?

edited Jul 9 at 18:41

Community♦

asked May 7 '15 at 15:20

Phairplay

662211

I've created the following formula:

=IFNA(LOOKUP(10^99,--MID(O2,MIN(IF((--ISNUMBER(--MID(O2,ROW($1:$25),1))=0)*ISNUMBER(--MID(O2,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25))),SUMPRODUCT(MID(0&RIGHT(N2,4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(N2,4),ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

It looks at column "N" and bring through just the number string. If there is an N/A it will then do the same on column "O" which is ran as an array and it works fine.

Yet when I converted it to VBA code, I get the error mentioned in the title .

Range("L2").Select

    Selection.FormulaArray = _

        "=IFNA(LOOKUP(10^99,--MID(RC[3],MIN(IF((--ISNUMBER(--MID(RC[3],ROW(R1:R25),1))=0)*ISNUMBER(--MID(RC[3],ROW(R2:R26),1)),ROW(R2:R26))),ROW(R1:R25))),SUMPRODUCT(MID(0&RIGHT(RC[2],4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(RC[2],4),ROW(R1:R25),1))* ROW(R1:R25),0),ROW(R1:R25))+1,1)*10^ROW(R1:R25)/10))"

What it going wrong?

excel excel-vba excel-formula vba

edited Jul 9 at 18:41

Community♦

asked May 7 '15 at 15:20

Phairplay

662211

edited Jul 9 at 18:41

Community♦

asked May 7 '15 at 15:20

Phairplay

662211

edited Jul 9 at 18:41

Community♦

edited Jul 9 at 18:41

Community♦

edited Jul 9 at 18:41

Community♦

asked May 7 '15 at 15:20

Phairplay

662211

asked May 7 '15 at 15:20

Phairplay

662211

asked May 7 '15 at 15:20

Phairplay

662211

.FormulaArray will only accept a 255 character or fewer string. Yours is too long.
– Rory
May 7 '15 at 15:25

what can I use in stead?
– Phairplay
May 7 '15 at 15:29

Can you show some sample data? It may be possible to shorten the formula.
– Rory
May 7 '15 at 15:46

You can put place holders in your formula to get around the limitation. As described here dailydoseofexcel.com/archives/2005/01/10/…
– Dick Kusleika
May 7 '15 at 15:52

give some screenshot of the data
– AHC
May 7 '15 at 21:40

add a comment |

.FormulaArray will only accept a 255 character or fewer string. Yours is too long.
– Rory
May 7 '15 at 15:25

what can I use in stead?
– Phairplay
May 7 '15 at 15:29

Can you show some sample data? It may be possible to shorten the formula.
– Rory
May 7 '15 at 15:46

You can put place holders in your formula to get around the limitation. As described here dailydoseofexcel.com/archives/2005/01/10/…
– Dick Kusleika
May 7 '15 at 15:52

give some screenshot of the data
– AHC
May 7 '15 at 21:40

.FormulaArray will only accept a 255 character or fewer string. Yours is too long.
– Rory
May 7 '15 at 15:25

what can I use in stead?
– Phairplay
May 7 '15 at 15:29

Can you show some sample data? It may be possible to shorten the formula.
– Rory
May 7 '15 at 15:46

You can put place holders in your formula to get around the limitation. As described here dailydoseofexcel.com/archives/2005/01/10/…
– Dick Kusleika
May 7 '15 at 15:52

give some screenshot of the data
– AHC
May 7 '15 at 21:40

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

thank you Dick for pointing me in the right directions and Rory for correcting my error

http://www.mrexcel.com/forum/excel-questions/853889-long-array-visual-basic-applications-issue.html

answered May 8 '15 at 10:08

Phairplay

662211

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f30105324%2ferror-unable-to-set-the-formulaarray-property-of-the-range-class%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

thank you Dick for pointing me in the right directions and Rory for correcting my error

http://www.mrexcel.com/forum/excel-questions/853889-long-array-visual-basic-applications-issue.html

answered May 8 '15 at 10:08

Phairplay

662211

add a comment |

up vote
0
down vote

thank you Dick for pointing me in the right directions and Rory for correcting my error

http://www.mrexcel.com/forum/excel-questions/853889-long-array-visual-basic-applications-issue.html

answered May 8 '15 at 10:08

Phairplay

662211

add a comment |

up vote
0
down vote

thank you Dick for pointing me in the right directions and Rory for correcting my error

http://www.mrexcel.com/forum/excel-questions/853889-long-array-visual-basic-applications-issue.html

answered May 8 '15 at 10:08

Phairplay

662211

thank you Dick for pointing me in the right directions and Rory for correcting my error

http://www.mrexcel.com/forum/excel-questions/853889-long-array-visual-basic-applications-issue.html

answered May 8 '15 at 10:08

Phairplay

662211

answered May 8 '15 at 10:08

Phairplay

662211

answered May 8 '15 at 10:08

Phairplay

662211

answered May 8 '15 at 10:08

Phairplay

662211

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ndtyjky