Use Range("A1").Text
代替.Value
发表评论编辑:
Why?
因为.Text
Range 对象的属性返回电子表格中字面上可见的内容,因此如果您的单元格显示例如i100l:25he*_92
那么Text将准确返回单元格中的内容,包括任何格式。
The .Value
and .Value2
属性返回单元格中存储的内容在引擎盖下排除格式化。特别.Value2
对于日期类型,它将返回十进制表示形式。
如果你想更深入地挖掘含义和性能,我刚刚发现this article http://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/这似乎是一个很好的指南
another edit
Here you go @Santosh
type in (MANUALLY) the values from the DEFAULT (col A) to other columns
Do not format column A at all
Format column B as Text
Format column C as Date[dd/mm/yyyy]
Format column D as Percentage
now,
paste this code in a module
Sub main()
Dim ws As Worksheet, i&, j&
Set ws = Sheets(1)
For i = 3 To 7
For j = 1 To 4
Debug.Print _
"row " & i & vbTab & vbTab & _
Cells(i, j).Text & vbTab & _
Cells(i, j).Value & vbTab & _
Cells(i, j).Value2
Next j
Next i
End Sub
and Analyse
输出!这真的很简单,我无能为力:)
.TEXT .VALUE .VALUE2
row 3 hello hello hello
row 3 hello hello hello
row 3 hello hello hello
row 3 hello hello hello
row 4 1 1 1
row 4 1 1 1
row 4 01/01/1900 31/12/1899 1
row 4 1.00% 0.01 0.01
row 5 helo1$$ helo1$$ helo1$$
row 5 helo1$$ helo1$$ helo1$$
row 5 helo1$$ helo1$$ helo1$$
row 5 helo1$$ helo1$$ helo1$$
row 6 63 63 63
row 6 =7*9 =7*9 =7*9
row 6 03/03/1900 03/03/1900 63
row 6 6300.00% 63 63
row 7 29/05/2013 29/05/2013 41423
row 7 29/05/2013 29/05/2013 29/05/2013
row 7 29/05/2013 29/05/2013 41423
row 7 29/05/2013% 29/05/2013% 29/05/2013%