what is wrong with my conditional formatting merge field code?

[nightale]

Hi guys,

I need values equal to or less than -1.5 to show up as bold and red and all other values (i.e. > -1.5) to show as normal, non bold black text. I've checked other threads and followed them carefully, but I simply can't figure out what's the problem with my syntax. Please look at the attached picture ;-)

Mergefield name is : DRS2TOTAEMSSSZ

Thanks!



11906

[macropod]

Try:
{={MERGEFIELD DRS2TOTAEMSSSZ}-1.5 \# "'{MERGEFIELD DRS2TOTAEMSSSZ}';'{MERGEFIELD DRS2TOTAEMSSSZ}'"}

[nightale]

Hi,

Thanks for the reply. I've tried it, and every negative value is now red and bold (no matter if it is greater or lesser than the specified criteria of -1.5). Also, I got "syntax error" when the value is positive. I did very carefully reproduce that code in Word.

Also, I'm trying to understand your code. Basically, you made it so every value that is positive after substracting 1.5 is black and regular font, while every value that stays negative is red and bold. But wouldn't it be the opposite? I mean, shouldn't it be "+1.5" instead of "-1.5" ?

Thanks

[macropod]

Sorry, yes it should have been:
{={MERGEFIELD DRS2TOTAEMSSSZ}+1.5 \# "'{MERGEFIELD DRS2TOTAEMSSSZ}';'{MERGEFIELD DRS2TOTAEMSSSZ}'"}
That said, even the previous version properly implemented would not have produced a syntax error.

[nightale]

11914

Hey there. Thanks. I don't see what's wrong. I've implemented correctly. I've used ctrl+f9 for each brackets... I've attached the word file too.

11913

[macropod]

Try executing the merge instead of just previewing it...

[nightale]

Just did and it doesn't work... I'm about to give up. How come is it that hard to do something that simple :-P

[macropod]

Are you sure your DRS2TOTAEMSSSZ field always has a numeric value? It cannot be empty or contain text or spaces.

[nightale]

Yes , here's the values of this field.



-2.33


0.67


-1.33


-1.67


-0.33


0.33


-0.33


1.33


-0.33


-1.67


0


-2


-2.33


-1


-0.33


-0.33


0


-0.33



If we can't find out what the problem is, I'll try with a dummy database and dummy field names to see what is wrong.

[nightale]

OMG! Finally found the culprit. You were right to investigate the source file. Guess what? When I exported my XLSX file to CSV, cells "type" changed from "number" to "general" (because I have 740 columns, I had to export my XLSX to CSV so that Merge mail was able to see more than 255 columns). So basically, when I exported to CSV, I lost the cell type formatting in my source xlsx file.

Edit : only thing I need to do, is now to keep the "+ sign" number formatting when values are positive (i.e. +0.00;-0.00) . Is it easy to do?

Thank you so much for your patience!

Regards,
Max

[macropod]

I've just made an Excel wkbk with your DRS2TOTAEMSSSZ field and data, attached your document to it, executed the mailmerge and everything worked as it should. No syntax/calculation errors!

The only changes I'd suggest are to embed the field in a QUOTE field (so you don't end up with a field in the output document) and to add some more formatting switches (to control the output precision):
{QUOTE{={MERGEFIELD DRS2TOTAEMSSSZ \# 0.00}+1.5 \# "'{MERGEFIELD DRS2TOTAEMSSSZ \# 0.00}';'{MERGEFIELD DRS2TOTAEMSSSZ \# 0.00}'"}}

This gives:
-2.33
0.67
-1.33
-1.67
-0.33
0.33
-0.33
1.33
-0.33
-1.67
0.00
-2.00
-2.33
-1.00
-0.33
-0.33
0.00
-0.33

[macropod]

1. A mailmerge with an Excel data source can handle more than 255 columns. There is no need to resort to CSV exports for this.
2. To insert the +/- simply use:
{QUOTE{={MERGEFIELD DRS2TOTAEMSSSZ \# 0.00}+1.5 \# "'{MERGEFIELD DRS2TOTAEMSSSZ \# +0.00;-0.00}';'{MERGEFIELD DRS2TOTAEMSSSZ \# 0.00}'"}}

[nightale]

Thanks for your help. I really appreciate it! I used that last code line you suggested (with QUOTE) and it worked flawlessly, thank you SO MUCH! I must have wasted like 4 hours on that... :-|

Regarding the mailmerge limit of 255, it seems I am not alone based on a google research (cannot post URL). I will probably make a pivot table with all the merge fields I need in another excel sheet, because my report document in word uses less than 255 merge fields. It'll be much simpler than exporting to CSV each time.

By the way, do you have any good starting place for tutorials on VB/VBA and this kind of programming ? I feel like I lack some bases to understand most of my problems.

Regards,
Max

[macropod]

The only limit regarding 255 fields is that Word's mailmerge tools won't offer more than that. Even so, you can insert them manually or, having used the CSV file to insert them, simply reconnect to the workbook.

do you have any good starting place for tutorials on VB/VBA and this kind of programming ?
I recently posted a fair bit of info here: http://www.excelforum.com/word-formatting-and-general/1023040-field-codes-use-in-mail-merge.html. Do note that this is Word field coding, not VBA.

[nightale]

Ok thanks for all this info. It's all good to know! Why isn't there a rep button so I can add + to your rep?

[macropod]

Why isn't there a rep button so I can add + to your rep?
I'm not across those issues. You can, however, rate the thread if you're so inclined.