Format Msgbox string from array of cells

[lross]

Hi,
I am trying to format a MsgBox string created from and array of cells. There are 3 columns in the cell range the 3rd of which have a % format. Msgbox writes the string as a decimal. Can anyone help with converting the range C2:C6 to a percentage the same as the source. I was trying to use the FORMAT() function without success

Cheers

>
Source data:
[TABLE="width: 295"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]24[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]49[/TD]
[TD]35%[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]99[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]999[/TD]
[TD]50%[/TD]
[/TR]
</tbody>[/TABLE]

Sub MsgBoxDiscount()
'Display the Discount Table in a popup
MsgString = Join(Evaluate("TRANSPOSE('Discounts'!A2:A6 & """ & vbTab & """ & 'Discounts'!B2:B6 & """ & vbTab & vbTab & """ & 'Discounts'!C2:C6 )"), vbNewLine)
MsgBox "Volume Range" & vbTab & "Discount" & vbCr & MsgString, , "Volume Discount Table"
End Sub

 

[Eric W]

Welcome to the Mr Excel Board!

Since you are creating the string using Evaluate, you need to use the worksheet function TEXT instead of Format. Try:

Sub MsgBoxDiscount()
'Display the Discount Table in a popup
MsgString = Join(Evaluate("TRANSPOSE('Discounts'!A2:A6 & """ & vbTab & """ & 'Discounts'!B2:B6 & """ & vbTab & vbTab & """ & TEXT('Discounts'!C2:C6,""0.00%"") )"), vbNewLine)
MsgBox "Volume Range" & vbTab & "Discount" & vbCr & MsgString, , "Volume Discount Table"
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Try

MsgString = Join(Evaluate("TRANSPOSE('Discounts'!A2:A6 & """ & vbTab & """ & 'Discounts'!B2:B6 & """ & vbTab & vbTab & """ & text('Discounts'!C2:C6,""0%"") )"), vbNewLine)

 

[lross]

Thanks. Works perfectly!