Cannot create text string

**jcprata** · 11-23-2009, 01:52 PM

Hi,

I am trying to create the following text string that matches this formula:

=SUMIF(A22:A60;"<>zEz";R22:R60)

The text string that i want, to be equals to the formula is the following:

"=P12+SUMIF(A" & FirstRow & ":A" & LastRow & "; "" <>zEz "";R" & FirstRow & ":R " & LastRow & ")"

When i run my problem i have an error in this text string, and the error is "application-defined or object-defined error".

Best Regards,
JP.

**DonkeyOte** · 11-23-2009, 02:01 PM

VBA in heavily Americanised - ie when writing formulae from VBA to cells always use US based syntax regardless of your own locale.

In this particular instance you should use a comma delimiter as opposed to the semi colon (as you use in native XL given your locale)

ie

=SUMIF(A22:A60;"<>zEz";R22:R60)

would be entered from VBA as

=SUMIF(A22:A60,"<>zEz",R22:R60)

regardless of client locale.... so in short change ; to , and you should be ok.

Note: once "written" to native XL the delimiters will be translated automatically - ie the commas will have become semi-colons.

**jcprata** · 11-24-2009, 07:15 AM

Hi,

I have tried that solution but didnt worked.
I think the problem is with the zEz string, in that criteria i want to compare the text string "zEz". (note that zEz is not an alias of a range/cells)

Best Regards,
JP.

**DonkeyOte** · 11-24-2009, 07:24 AM

JP, I did not notice previously but you have a space in your string after the last reference to column R (prior to insertion of LastRow variable) and this is not valid syntax - if you correct for that your formula should work without issue (semi colon point holds).

EDIT: I should add that if you use .FormulaLocal property as opposed to standard .Formula you can enter with locale specific delimiters