currency in a concatenated string

[dattagal]

Hi guys



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]

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 [vba]Sheets("Questions").Cells(43, 4)[/vba]Use [vba]Sheets("Questions").Cells(43, 4).Text[/vba]That will pull what is being shown in the cell.

Matt

[Bob Phillips]

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]

I may be slightly off track here but i tink its done like this:
[vba]
MsgBox Format(0#, "$") & Range("A1").Value
[/vba]

[Simon Lloyd]

Ermm.....not only was i slow but wrong!
Tut!

[dattagal]

Awesome!!! and you're so quick too!

thanks so much! this is so cool!