Undo Removal of Trailing Zeros

[NetFlash]

Hi All,

I have a table like this:
<table width="128" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height:12.75pt" height="17"><td class="xl24" style="height:12.75pt;width:48pt" height="17" width="64">Weight</td> <td class="xl25" style="width:48pt" width="64">Index</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl26" style="height:12.75pt" height="17" align="right">200</td> <td class="xl27" align="right">0.164</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">1000</td> <td class="xl28" align="right">0.187</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">2000</td> <td class="xl28" align="right">0.442</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">3000</td> <td class="xl28" align="right">-0.701</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">4000</td> <td class="xl28" align="right">-1.103</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">5000</td> <td class="xl28" align="right">0.002</td></tr></tbody></table>
And wish to create an output string to put in a single cell (F3) that looks like this:
1/Code/INS/200/0.16*1000/0.19*2000/0.44*3000/0.70-*4000/1.10-*5000/0.00

The entry starts with: 1/Code/INS
The values from one row are concated with: /
The values of the multiple rows are concated with: *
The (-) for any negative values should be placed behind the values
The decimal points for the index should be 2.

I do Not want to convert the values to text. They have to stay numeric as they have to be rounded off automatically, and are also used in formulas for other cells.
I tried Tools>Options>Calculations>Precision as Displayed.
This works well with numbers not ending on 0. But numbers ending with 0 still get truncated.

Sub Code
Dim Rng As Range, Dn As Range, Txt As String
Dim colB As String
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Txt = "1/Code/INS/"
    For Each Dn In Rng
        colB = IIf(Dn.Offset(, 1) < 0, Abs(Dn.Offset(, 1)) & "-", Dn.Offset(, 1))
        Txt = Txt & Dn & "/" & colB & "*"
    Next Dn
[F3] = Left(Txt, Len(Txt) - 1)
End Sub

Any and all suggestions are welcome

 

[wigi]

Did you try the Format function in VBA? It is similar to the TEXT function in Excel.

You can provide a string to indicate how you want to format the number of the cell.