vba - 将单元格中的数据设置为正在显示的日期 [英] vba - set data in cell to what date is being displayed

问题描述

可能是一个简单的问题，我只是无法确定我的数字，因为我仍然不完全了解如何处理excel中的不同层（与正在显示的内容相比，数据实际上在什么位置）

Probably a simple question, I just cannot pinpoint my figure on it as I still do not fully understand how to approach the different "layers" in excel (what the data actually is in a cell compared to what is being displayed...Ex. date)

我的单元格内有以下数据

I have the following data inside of my cells

10/17/2014
10/18/2014
10/19/2014
10/20/2014

我运行以下vba代码，希望重新格式化数据：

I ran the following vba code to hopefully reformat that data:

Sheet1.Range("C2", "C1000").NumberFormat = "dd-mmm-yyyy"

这使我的数据显示为：

17-Oct-2014
18-Oct-2014
19-Oct-2014
20-Oct-2014

哪个是我想要的是。唯一的问题是如果我点击实际单元格内部的数据仍然是 10/18/2014 格式。

Which is actually what I want to be. Only problem is if I click inside of the actual cell, the data is still in 10/18/2014 format.

现在从我的理解格式将只会改变数据的显示方式。我似乎无法在网上找到如何在屏幕上显示什么，并将单元格的值更改为正在显示的内容。我会认为这是一个受欢迎的问题，反复问道，也许我的搜索技巧刚刚结束，我找不到文章。任何帮助将不胜感激。谢谢你。

Now from my understanding "format" will just change how the data is displayed. I cannot seem to find online how to take what is displaying on the screen and change the value of the cell to what is being displayed. I would think this is a "popular" issue and asked repeatedly so perhaps my search skills are just off and I couldn't locate the article. Any help would be appreciated. Thank you.

我已经看过我可以使用粘贴特殊，但这需要我格式化单元格，然后手动复制和粘贴值，这不是我想。我希望在VBA内部完全自动化。

I have read that I could use "Paste Special", but this requires me to format cells and then copy and paste the values manually which is not what I would like. I would like to hopefully completely automate this inside of VBA.

推荐答案

尝试这样：

Sheet1.Range("C1:C1000").Value = Sheet1.Evaluate("""'"" & INDEX(TEXT(C1:C1000,""dd-mmm-yyyy""),)")

code>在开始强制输出文本。

The ' at the beginning forces the output into text.

你也可以这样做：

Sheet1.Range("C1:C1000").NumberFormat = "@"
Sheet1.Range("C1:C1000").Value = Sheet1.Evaluate("INDEX(TEXT(C1:C1000,""dd-mmm-yyyy""),)")

这将将格式化为日期的数字转换为看起来像日期的文本。换句话说，如果这些单元格上的任何引用或数学公式都需要将它们隐藏回数字。

This will turn numbers that are formatted as dates to text that look like dates. In other words if any reference to or mathematical formulas on these cells will need to covert them back to numbers.

这篇关于vba - 将单元格中的数据设置为正在显示的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！