Help With Formula Array! 1

[APElliott]

Hello,

Would anyone please tell me where I'm going wrong with the bits of code below:

[E2].FormulaArray = &quot;=SUM(if(B2 <> B1, D2, E1& &quot; &quot; & D2))&quot;

[F2].FormulaArray = &quot;=SUM(if(a2 <> a3, &quot;1&quot;, &quot;0&quot)&quot;


Thanks in advance!

Andrew

 

[SkipVought]

Drew,

What are you trying to do?

1. You are not dealing with an array

2. what is (E1& &quot; &quot; & D2) --a string-- doing in a SUM formula?

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[APElliott]

Hi Skip,

You've probably noticed - these are the formulae you did for me the other day. I tried the code below but got another error statement.

[E2].FormulaArray = &quot;=if(B2 <> B1, D2, E1& &quot; &quot; & D2)&quot;

[F2].FormulaArray = &quot;=if(a2 <> a3, &quot;1&quot;, &quot;0&quot&quot;

Thanks,

ANDREW

 

[SkipVought]

What is the thread reference so I can check the answer I gave you in context.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[APElliott]

Hi Skip,

All that I want to do is insert your formulae into the cells by using a bit of code.

I've been searching through other peoples old threads and thought I'd found the answer, but obviously not.

Cheers Skip!!

thread707-667644

Thanks,

Andrew

 

[SkipVought]

My answer in that thread was regarding assembling a string. You are trying to calculate a SUM.

APPLES and ORANGES!

What are you trying to do? Your original post does not make ANY sense!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

sure you wanna use formulaarray??? - not formula or formulaR1C1 ???

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[APElliott]

Hi Skip, Hi Geoff,

I'm not at all sure what I should be using in all honesty!

I've tried a few different things - formula etc, but got errors on each occasion, so I best start from the begining.

Ok - Rather than type then drag the formula's below, I was trying to write some code that would automatically insert them.

Firstly I wanted this formula '=if(B2 <> B1, D2, E1& &quot; &quot; &D2)'
inserting into E2 and then into the last row of E and the rows in between (the last row being determined by data in Row A.

And then I wanted this formula '=if(a2 <> a3, &quot;1&quot;, &quot;0&quot' inserting into the same rows in column F.

I have tried looking around to find the answer, hoping that I wouldn't have to disturb you guys.

Thanks,

Andrew

 

[xlbo]

in that case:

lRow = cells(65536,1).end(xlup).row
range(&quot;E2:E&quot; & lRow).formula = &quot;=if(B2 <> B1, D2, E1& &quot;&quot; &quot;&quot; &D2)&quot;
range(&quot;F2:F&quot; & lRow).formula = &quot;=if(a2 <> a3, &quot;&quot;1&quot;&quot;, &quot;&quot;0&quot;&quot&quot;


will work for ya

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[APElliott]

Cheers - You are a Jedi

Works a treat!!!

Thanks

Andrew

 

[xlbo]

It was the &quot; &quot; giving you trouble rather than anything else (and formulaarray should be used for array formulae only)

If you are trying to insert a formula via VBA, the one thing you must remember is that the formula is passed as a string so &quot;&quot; needs quotes round it &quot;&quot;&quot;&quot;

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244