vba Excel - 复制显示值而不是实际值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8847115/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel - Copy the displayed value not the actual value
提问by Paul Aitken
I am a pilot, and use a logbook program called Logten Pro. I have the ability to take excel spreadsheets saved from my work flight management software, and import them into Logten Pro using the CSV format.
我是一名飞行员，使用名为 Logten Pro 的日志程序。我有能力获取从我的工作飞行管理软件中保存的 Excel 电子表格，并使用 CSV 格式将它们导入 Logten Pro。
My problem however, is that the work flight management software, exports the date and time of take-off of a flight into one cell in the following excel format: DD/MM/YYYY H:MM:SS PM. This is handled fine by Excel, and is formatted by default to DD/MM/YY even though the actual value is more specific, comprising of the full length date and time group.
然而，我的问题是工作飞行管理软件将航班起飞的日期和时间以以下 excel 格式导出到一个单元格中：DD/MM/YYYY H:MM:SS PM。这由 Excel 处理得很好，默认情况下格式化为 DD/MM/YY，即使实际值更具体，包括全长日期和时间组。
This is a problem because Logten Pro will only auto-import the date if it is in DD/MM/YY format, and there is no way to pull out just the displayed DD/MM/YY date rather than the full date time group actual value, unless you manually go through and delete the extra text from the function box.
这是一个问题，因为 Logten Pro 只会在日期为 DD/MM/YY 格式时自动导入日期，并且无法仅提取显示的 DD/MM/YY 日期而不是实际的完整日期时间组值，除非您手动检查并从功能框中删除多余的文本。
My question is: Is there a VBA macro that can automatically copy the actual displayed text, and paste it into another cell, changing the actual value as it does, to just the DD/MM/YY value? Additionally, can this be made to work down a whole column rather than individual cells at a time?
我的问题是：是否有一个 VBA 宏可以自动复制实际显示的文本，并将其粘贴到另一个单元格中，将实际值更改为 DD/MM/YY 值？此外，这是否可以一次处理整个列而不是单个单元格？
Note I have no VBA experience so the perfect answer would just be a complete VBA string I could copy and paste.
注意我没有 VBA 经验，所以完美的答案只是一个完整的 VBA 字符串，我可以复制和粘贴。
Thank You.
谢谢你。
回答by JMax
As pointed out in the comments, you'd better not use VBA but formulas instead.
正如评论中指出的那样，您最好不要使用 VBA，而是使用公式。
This formula:
这个公式：
TEXT(A1,"dd-mm-yyy")
will return the formated date in a text way. You can drag and drop the formula in the whole range of your cells and Copy/Paste Special> Valuesso that you will only have the needed values to get imported in Logten Pro.
将以文本方式返回格式化的日期。您可以在整个单元格范围内拖放公式，然后Copy/Paste Special>Values以便您将只有需要的值才能在 Logten Pro 中导入。
回答by Robert Mearns
There are three options using formulas.
有使用公式的三个选项。
Rounddown
舍入
Excel stores the date time as a number and uses formatting to display it as a date. The format is date.time, where the integer is the date and the fraction is the time.
Excel 将日期时间存储为数字并使用格式将其显示为日期。格式为 date.time，其中整数是日期，分数是时间。
As an example 01/01/2012 10:30:00 PM is stored as 40909.9375
例如 01/01/2012 10:30:00 PM 存储为 40909.9375
All the values after the decimal place relate to the hours and minutes
小数点后的所有值都与小时和分钟有关
So a formula could be used to round the number down to a whole number.
因此，可以使用公式将数字四舍五入为整数。
=ROUNDDOWN(A1,0)
Then format the value as a short date. It will then display as 01/01/2012
然后将值格式化为短日期。然后它将显示为 01/01/2012
INT
情报局
As above, but using a different formula to get rid of the fraction (time)
同上，但使用不同的公式来去除分数（时间）
=INT(A1)
Text
文本
Alternately the date only could be extracted as text using this formula
或者，只能使用此公式将日期提取为文本
=TEXT(A1,"dd/mm/yyyy")
It will then display as 01/01/2012
然后它将显示为 01/01/2012
回答by Andre Britton
I'm a bit late to the party on this one, but recently came across this as was searching for answers to a similar problem.
我参加这个聚会有点晚了，但最近在寻找类似问题的答案时遇到了这个问题。
Here is the answer I finally came up with.
这是我最终想出的答案。
Option Explicit
Sub ValuesToDisplayValues()
Dim ThisRange As Range, ThisCell As Range
Set ThisRange = Selection
For Each ThisCell In ThisRange
ThisCell.Value = WorksheetFunction.Text(ThisCell.Value, ThisCell.NumberFormat)
Next ThisCell
End Sub
This answers the question as asked, apart from the new values are pasted over the existing ones, not into a new cell, as there is no simple way to know where you would want the new values to be pasted. It will work on the whole range of selected cells, so you can do a whole column if needed.
这回答了所问的问题，除了将新值粘贴到现有值上，而不是粘贴到新单元格之外，因为没有简单的方法可以知道您希望将新值粘贴到何处。它将适用于整个选定单元格范围，因此您可以根据需要执行一整列。
