强制输入"F2" +"Enter"在细胞范围内 [英] Force "F2"+"Enter" on range of cells

问题描述

我有一个Excel 2010工作表，该工作表具有用于将其他工作表中的数据复制到另一工作表上的特定格式的宏.

数据已复制，但是保存日期或时间值的单元格区域格式存在问题.

数据来自数据库摘录，所有内容均为文本格式.在我通过VBA复制日期的工作表中，日期的格式为"yyyy-mm-dd"，时间的格式为"hh:mm.ss.ss".

行数永远不会固定，因此我设置了VBA代码以将格式应用于单元格范围，例如:

AssDateLastRow = shAss.Range("C" & Rows.Count).End(xlUp).Row

shAss.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd"

不是该范围内的所有单元格都具有正确的格式，它们将显示为15/04/2014而不是2014-04-15.如果我手动选择单元格并按 F2 ，然后按 ENTER 键，则该格式将显示为我需要的格式.这种情况在整个范围内都是随机发生的，可能有成千上万的行，因此尽管在工作表上分别手动按 F2 + ENTER 进行拖曳是不现实的.

我看了网上，发现应该如何使用VBA自动执行 F2 + ENTER .

下面的代码是从一组较大的代码行中提取的，因此Dim语句等在实际副本中更靠前，但这应该显示到目前为止我解决该问题的方式.

Dim shAss As Worksheet
Dim AssDateLastRow As Long
Dim c As Range

'enter method to format 'Date Craftperson Assigned' and 'Time Craftperson Assigned' in   Assignments sheet
'column "C" and "D", to formats required by Archibus: date "yyyy-mm-dd", time  "hh:mm.ss.ss"
AssDateLastRow = shAss.Range("C" & Rows.Count).End(xlUp).Row
shAss.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd"
'ensure format is applied by forcing F2 edit of cell
For Each c In shAss.Range("C4:C" & AssDateLastRow).Cells
    c.Select
    SendKeys "{F2}", True
    SendKeys "{ENTER}", True
'Selection.NumberFormat = "yyyy-mm-dd"
Next

运行代码时，数据会复制到我的工作表中，但日期和时间仍然是混合格式.

通过VBA强制 F2 + ENTER 的尝试似乎没有做任何事情.如果手动完成，就可以了.

下面是从工作表中的结果中复制数据的示例

Work Request Code       Date Assigned       Time  Assigned
92926                   19/05/2014          14:30.00.00
92927                   19/05/2014          15:00.00.00
92928                   2014-05-19          15:15.00.00
92934                   2014-05-19          14:00.00.00
92527                   12/05/2014          07:30
92528                   12/05/2014          08:00
92804                   2014-05-12          16:15
92805                   2014-05-12          16:20.00.00

解决方案

我使用这个简单的宏在当前选定的范围内应用 F2 + Enter :

Sub ApplyF2()
    Selection.Value = Selection.FormulaR1C1
End Sub

I have an Excel 2010 worksheet which has macros to copy data from other sheets into a specific format on another sheet.

The data copies but I have an issue with the formatting of cell ranges which hold date or time values.

The data originates from a database extract and everything is in text format. In my worksheet when I copy the date (via VBA) I apply the format "yyyy-mm-dd" for dates and "hh:mm.ss.ss" for times.

There is never a fixed amount of rows so I've set the VBA code to apply the formatting to the range of cells for example:

AssDateLastRow = shAss.Range("C" & Rows.Count).End(xlUp).Row

shAss.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd"

Not all cells in the range have the correct format, they will appear as 15/04/2014 not 2014-04-15. If I manually select the cell and press the F2 then ENTER keys the format appears as I need. This happens randomly through the range and there could be thousands of rows so it is not practical to trawl though the worksheet manually hitting F2+ENTER on each one.

I've looked on the internet and found what should automatically do the F2+ENTER with VBA.

The code below is extracted from a larger set of lines of code, so the Dim statements etc. are further up in the actual copy, but this should show the way I've tackled this so far.

Dim shAss As Worksheet
Dim AssDateLastRow As Long
Dim c As Range

'enter method to format 'Date Craftperson Assigned' and 'Time Craftperson Assigned' in   Assignments sheet
'column "C" and "D", to formats required by Archibus: date "yyyy-mm-dd", time  "hh:mm.ss.ss"
AssDateLastRow = shAss.Range("C" & Rows.Count).End(xlUp).Row
shAss.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd"
'ensure format is applied by forcing F2 edit of cell
For Each c In shAss.Range("C4:C" & AssDateLastRow).Cells
    c.Select
    SendKeys "{F2}", True
    SendKeys "{ENTER}", True
'Selection.NumberFormat = "yyyy-mm-dd"
Next

When I run the code, the data copies into my worksheets but the dates and times are still in a mixed format.

The attempt at forcing the F2+ENTER via the VBA doesn't seemed to have done anything. If done manually it works okay.

Below is an example of data copied from the results in the worksheet

Work Request Code       Date Assigned       Time  Assigned
92926                   19/05/2014          14:30.00.00
92927                   19/05/2014          15:00.00.00
92928                   2014-05-19          15:15.00.00
92934                   2014-05-19          14:00.00.00
92527                   12/05/2014          07:30
92528                   12/05/2014          08:00
92804                   2014-05-12          16:15
92805                   2014-05-12          16:20.00.00

解决方案

I use this simple macro to apply F2 + Enter on the currently selected range:

Sub ApplyF2()
    Selection.Value = Selection.FormulaR1C1
End Sub

这篇关于强制输入"F2" +"Enter"在细胞范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！