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:

1	10	0%
11	24	10%
25	49	35%
50	99	40%
100	999	50%

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

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!