Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array and not loseing trailing zero's 123.00
I have generated VBA code to place a worksheet range in a comment. To format
the text for the comment I rely on application.worksheetfunction.len or max referring to the named range(slow). I am trying to move the data into a array and do the process differently with the same result. So far only one problem. If the contents in one cell of named range ends on a even dollar then the array only recognizes the dollar and not the 0s in the length of number. ?? Is there a way for the array to grab (new VBA term) the data from the named range as it's formatted for display or rounded for display? -- Jim at Eagle |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array and not loseing trailing zero's 123.00
On Apr 15, 12:56*pm, Jim at Eagle
wrote: I have generated VBA code to place a worksheet range in a comment. To format the text for the comment I rely on application.worksheetfunction.len or max referring to the named range(slow). I am trying to move the data into a array and do the process differently with the same result. So far only one problem. If the contents in one cell of named range ends on a even dollar then the array only recognizes the dollar and not the 0s in the length of number. ?? Is there a way for the array to grab (new VBA term) the data from the named range as it's formatted for display or rounded for display? *-- Jim at Eagle Try posting your code if this doesn't help. I'm not sure how you're putting it back into the comment from the array, but try using the Format function to force the value into the format you're wanting. For example: Dim value as Double value = 123 Range("A2").AddComment Format(value, "0.00") Hope that helps, Cory |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array and not loseing trailing zero's 123.00
Arrays hold data. They do not hold any formatting. If you want to output the
value of an array in a formatted manner then you can use the Format function something like this... Sub test() Dim ary(3) As Double Dim lng As Long ary(0) = 1.23 ary(1) = 2 ary(2) = 4.567 For lng = 0 To 2 MsgBox Format(ary(lng), "#,##0.00") Next lng End Sub -- HTH... Jim Thomlinson "Jim at Eagle" wrote: I have generated VBA code to place a worksheet range in a comment. To format the text for the comment I rely on application.worksheetfunction.len or max referring to the named range(slow). I am trying to move the data into a array and do the process differently with the same result. So far only one problem. If the contents in one cell of named range ends on a even dollar then the array only recognizes the dollar and not the 0s in the length of number. ?? Is there a way for the array to grab (new VBA term) the data from the named range as it's formatted for display or rounded for display? -- Jim at Eagle |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array and not loseing trailing zero's 123.00
Hello,
I suggest to use something like: Function grab(r As Range, _ Optional sdel As String = " ") As String Dim v, s As String For Each v In r grab = grab & s & v.Text s = sdel Next v End Function Sub testme() Range("D1").ClearComments Range("D1").AddComment grab(Range("myrange")) End Sub Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero's | Excel Worksheet Functions | |||
Formatting numbers with leading and trailing zero's | Excel Worksheet Functions | |||
Zero's | Charts and Charting in Excel | |||
Zero's # 2 | Excel Discussion (Misc queries) | |||
Zero's | New Users to Excel |