Copying formula from a String in VBA to cell on worksheet 2

[Statistician]

Hi

I'm having problems copying a string variable to a cell formula in excel.

The string is over 300 characters long and if I type this directly into a worksheet cell it works fine (i.e., the logic is correct as far as the formula goes).

The string is simply a long IF statement.

However, when trying perform this action using VBA it doesn't work. All I am using is the activecell.formula="=(IF statement)".

I have checked and the max lenth of a string is huge by comparison so I'm confused?

Can anyone offer some advice please?

 

[PHV]

No quotes in your "long formula" ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AnotherHiggins]

Turn on the macro recorder (Tools > Macro > Record New Macro) then paste the formula directly into a cell.

Observe the code that was generated to find the difference.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Statistician]

Hi

Thanks for responding.

No, there are no quotes in the formula itself, only around the string expression. eg., "=IF(a>b,1,0)"

Also, when I attempt to record a macro it falls over and doesn't work.

A colleague informed me that the string size was infact restricted to 255 characters...although he's not 100% sure.
I'm currently trying things like concatenating etc....

 

[SkipVought]

"Also, when I attempt to record a macro it falls over and doesn't work."

Do you have macros disabled?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[AnotherHiggins]

-> Also, when I attempt to record a macro it falls over and doesn't work.

Yes, that isn't very descriptive.

What happens when you try to record the macro? If any code was generated, please post it.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

There are sometimes issues in string lengths of over 255 characters but this is normally only an issue when copy / pasting

Please post the relevant portion of your code along with the formula you are trying to insert...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Statistician]

Apologies for vague description. The macros are not disabled by the way.

After attempting to record the macro, excel states it was unable to record the macro. After stopping the recorder, looking at the macro code - it is simply blank.

The line of code I am currently trying to use is as follows:

ActiveCell.FormulaR1C1 = "=IF(AND($D38>=E$4,$D38<=E$5),((ROUNDDOWN(E$15*networkdays(E$4,$D38,
BankHolsArray),0)-ROUNDDOWN(E$15*(networkdays(E$4,$D38,BankHolsArray)-1),
0))*ClipSize*E$12),IF(AND($D38>=E$7,$D38<=E$8),((ROUNDDOWN(E$26*networkdays
(E$4,$D38,BankHolsArray),0)-ROUNDDOWN(E$26*(networkdays(E$4,$D38,
BankHolsArray)-1),0))*ClipSize),""))"

BankHolsArray is simply a list of dates (named range on worksheet). Clipsize is a named range too (just a simple numeric value).

Typing or copying the formula in manullay works fine.

Cheers all.

 

[Statistician]

Also, I tried changing the "FormulaR1C1" after the "activecell." to just "Formula" when I identified the mistake but it had no effect.

 

[AnotherHiggins]

No quotes in your "long formula" ?

No, there are no quotes in the formula itself, only around the string expression. eg., "=IF(a>b,1,0)"

But you do have quotes within the formula.

The reason that was asked is because in VBA, you need to surround any quotes that you want to end up in the cell within another set of quotes.

Like this (changes highlighted and in red):

Range("A1") = "=IF(AND($D38 >= E$4, $D38 <= E$5), ((ROUNDDOWN(E$15 * networkdays(E$4, $D38, BankHolsArray), 0) - ROUNDDOWN(E$15 * (networkdays(E$4, $D38, BankHolsArray) - 1), 0)) * ClipSize * E$12), IF(AND($D38 >= E$7, $D38 <= E$8), ((ROUNDDOWN(E$26 * networkdays(E$4, $D38, BankHolsArray), 0) - ROUNDDOWN(E$26 * (networkdays(E$4, $D38, BankHolsArray) - 1), 0)) * ClipSize), [highlight][red]"[/red][/highlight]""[highlight][red]"[/red][/highlight]))"

For future reference, you'll get answers more quickly if you fully describe the problem and show us your code/formula in the first place.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[MakeItSo]

I just tried this with a reeeaaally long formula like this.

=1+2+3+4+5+56+67+7+8+89+9+9+7+6+5+4+4+3 * 68746541231*84564654651231 / 54654131 - 542415347984561000 + 687987865121687000- 45646549 / 74565 + 1+2+3+4+5+56+67+7+8+89+9+9+7+6+5+4+4+3 * 68746541231 * 84564654651231 / 54654131 - 542415347984561000 + 687987865121687000 - 45646549 / 74565-3654 ...[and it goes on]

It worked fine - after I corrected a typo!

First I got an error 1004. So, I simply pasted it into an activated cell (not just selected one and pasted, but activated it by double click and pasted then), hit return and:
voila: "your formula contains an error. Will you accept the following proposed formula?"

Hell yeah! replaced my String formula with the corrected one and bingo!
And it definitely IS > 255 chars...

So most probably it is simply your formula that is faulty.

if in doubt, add a

Dim myFrmla as String
myFrmla="=IF(AND($D38 >= E$4, $D38 <= E......."
debug.print myFrmla

Copy the formula from the immediate window (Ctrl+G) and paste it into your Excel cell. See if XL accepts your formula at all...

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[Statistician]

Thanks anotherhiggins...that advice was spot on and it worked a treat.

Apologies again, I will endeavour to try to be more specific and accurate in the future.

Your help was much appreciated.

 

[AnotherHiggins]

No problem. I'm glad you got it sorted out.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.