将范围设置为值而不重新格式化单元格 [英] Set range to value without re-formatting cell
问题描述
我有一系列数字,其中有一些 /
,我试图复制到另一个范围:
...
152/7163
152/7163
153/7163
153/7163
48/7164
332/7138
332/7138
332/7138
371/7166
5/7161
5/7161
5/7161
5/7161
115/7163
116/7163
121/7163
519/7132
154/7163
...
但是,当我这样做时,其中一些被格式化为一个日期(即 5 / 7161
成为 May-61
。请注意,在转移到我的 importWS
中,Excel添加了5 / 1 / 7161 ...然后将其转换为 May-61
,如果我将其更改为号码
然后单元格将其值完全更改为 1921663.00
。如何防止这种情况发生?
我正在从 ws
复制并将复制到 importWS
:
importWS.range(importWS.ce lls(2,4),importWS.cells(lastRow,4))Value = _
并且都不工作。我知道我可以循环遍历范围内的每个单元格,并像
ws.Range(ws.cells(2,7),ws.cells(lastRow,7))值$ b我试过使用.Value2
和$ code> .TextimportWS.cells(i,4).Value ='& ws.cells(i,7).Value
但宁可不要,因为我使用上面的范围方法为多列执行此操作。解决方案预设范围。 NumberFormat属性到文本,然后阅读 Range.Value2属性< a>从源头。
with importWS
.range(.cells(2,4),.cells lastRow,4))NumberFormat =@
.range(.cells(2,4),.cells(lastRow,4))= _
ws.Range(ws.cells(2, 7),ws.cells(lastRow,7))。value2
end with
I have a range of numbers with some
/
in it that I am trying to copy to another range:... 152/7163 152/7163 153/7163 153/7163 48/7164 332/7138 332/7138 332/7138 371/7166 5/7161 5/7161 5/7161 5/7161 115/7163 116/7163 121/7163 519/7132 154/7163 ...
However, when I do so, some of those get formatted to a date (i.e.
5/7161
becomesMay-61
. Note that in the transfer to myimportWS
, Excel added 5/1/7161 on its own...then turned that intoMay-61
. If I change it toNumber
then the cell changes its value completely, to1921663.00
. How do I prevent that from happening?I'm copying from
ws
and copying toimportWS
:importWS.range(importWS.cells(2,4),importWS.cells(lastRow,4)).Value = _ ws.Range(ws.cells(2,7),ws.cells(lastRow,7)).Value
I've tried using
.Value2
and.Text
and neither work. I know I could loop through each cell in the range, and do likeimportWS.cells(i,4).Value = "'" & ws.cells(i,7).Value
but would rather not, since I'm using the above range method to do this for multiple columns.解决方案Preset the Range.NumberFormat property to Text then read the Range.Value2 property from the source.
with importWS .range(.cells(2, 4), .cells(lastRow, 4)).NumberFormat = "@" .range(.cells(2, 4), .cells(lastRow, 4)) = _ ws.Range(ws.cells(2, 7), ws.cells(lastRow, 7)).value2 end with
这篇关于将范围设置为值而不重新格式化单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!