PDA

View Full Version : currency in a concatenated string



dattagal
05-17-2007, 10:23 AM
Hi guys

:banghead:

Would appreciate some help with currency formats in a concatenated string.

I have a string that include references to cells in another worksheet. The string works just fine but cell reference parts are supposed to be dollar amounts...


Sheets("FormEnglish").Cells(73, 1) = "If you are enrolling for [product] in excess of the GI amount of" & " " & Sheets("Questions").Cells(43, 4) & " " & "or if your spouse is enrolling for coverage in excess of 50% of the amount that you enroll for or" & " " & Sheets("Questions").Cells(43, 5) & " " & ", you must complete and submit an EOI. The form is available from your employer/benefits administrator, or is available online at...


Instead, it comes out like this...

If you are enrolling for [product] in excess of the GI amount of 500000 or if your spouse is enrolling for coverage in excess of 50% of the amount that you enroll for or 150000 , you must complete an and submit and Evidence of Insurability form. The form is available from your employer/benefits administrator, or is available online at...

Is there some way I can format the amounts to be currency?

Any assistance will be greatly appreciated.


Cindy

mvidas
05-17-2007, 10:43 AM
Hi Cindy,

You could use the Format() function around the cell reference along with a currency number format, but if the cell is already formatted the way you want it, use .Text after the cell instead of the default property of .Value you are (unknowingly) using
So instead of Sheets("Questions").Cells(43, 4)Use Sheets("Questions").Cells(43, 4).TextThat will pull what is being shown in the cell.

Matt

Bob Phillips
05-17-2007, 10:43 AM
Sheets("FormEnglish").Cells(73, 1) = "If you are enrolling for [product] in excess of the GI amount of" & " " & Format(Sheets("Questions").Cells(43, 4),"$#,##0.00") & " " & "or if your spouse is enrolling for coverage in excess of 50% of the amount that you enroll for or" & " " & Format(Sheets("Questions").Cells(43, 5),"$#,##0.00") & " " & ", you must complete and submit an Evidence of Insurability form. The form is available from your employer/benefits administrator, or is available online at...

Simon Lloyd
05-17-2007, 10:48 AM
I may be slightly off track here but i tink its done like this:

MsgBox Format(0#, "$") & Range("A1").Value

Simon Lloyd
05-17-2007, 10:51 AM
Ermm.....not only was i slow but wrong!
Tut!

dattagal
05-17-2007, 10:54 AM
Awesome!!! and you're so quick too!

thanks so much! this is so cool!