vba 在单元格范围内强制“F2”+“Enter”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24060831/
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
Force "F2"+"Enter" on range of cells
提问by dan1974
I have an Excel 2010 worksheet which has macros to copy data from other sheets into a specific format on another sheet.
我有一个 Excel 2010 工作表，它具有将数据从其他工作表复制到另一个工作表上的特定格式的宏。
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.
数据源自数据库提取，所有内容均为文本格式。在我的工作表中，当我复制日期（通过 VBA）时，我应用了"yyyy-mm-dd"日期和"hh:mm.ss.ss"时间的格式。
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:
从来没有固定数量的行，所以我设置了 VBA 代码以将格式应用于单元格范围，例如：
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/2014not 2014-04-15. If I manually select the cell and press the F2then ENTERkeys 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+ENTERon each one.
并非该范围内的所有单元格都具有正确的格式，它们将显示为15/04/2014not 2014-04-15。如果我手动选择单元格并按F2thenENTER键，则格式会根据需要显示。这种随机发生过范围，可能会有数千行，因此它是不实际的拖网，虽然工作表手动打F2+ENTER上的每一个。
I've looked on the internet and found what should automatically do the F2+ENTERwith VBA.
我在互联网上查看并发现应该使用 VBA自动执行F2+ 的操作ENTER。
The code below is extracted from a larger set of lines of code, so the Dimstatements etc. are further up in the actual copy, but this should show the way I've tackled this so far.
下面的代码是从更大的代码行中提取的，因此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
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+ENTERvia the VBA doesn't seemed to have done anything. If done manually it works okay.
通过 VBA强制使用F2+的尝试ENTER似乎没有做任何事情。如果手动完成，它工作正常。
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
回答by Thomas F.
I use this simple macro to apply F2+ Enteron the currently selected range:
我使用这个简单的宏在当前选择的范围上应用F2+ Enter：
Sub ApplyF2()
Selection.Value = Selection.FormulaR1C1
End Sub
回答by Robert Mearns
I can think of two options to get Excel to apply the formatting to the cells in one step.
我可以想到两个选项来让 Excel 在一步中将格式应用于单元格。
The first is to use the Text to columnsfunctionality even though there is nothing in the column to split. The second option is to copy a value of 1 and paste it into the cells using the Paste Special - Multiplyoption.
第一种是使用文本到列功能，即使列中没有要拆分的内容。第二个选项是复制值 1 并使用选择性粘贴 - 乘法选项将其粘贴到单元格中。
Although either method should force an update of the cell formating, I would lean towards the first option. This is incase some of your dates are is stored as text.
尽管任何一种方法都应该强制更新单元格格式，但我倾向于第一个选项。这是因为您的某些日期存储为文本。
Sub Format_Text_to_Columns()
Dim AssDateLastRow As Long
AssDateLastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd;@"
'Set the format
Range("C4:C" & AssDateLastRow).Select
Selection.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
Space:=True, FieldInfo:=Array(1, 5)
'Use text to columns to force a format update
End Sub
Sub Format_Paste_Special_Multiply()
Dim AssDateLastRow As Long
AssDateLastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd;@"
'Set the format
Range("C1").FormulaR1C1 = "1"
Range("C1").Copy
Range("C4:C" & AssDateLastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
Application.CutCopyMode = False
Range("C1").ClearContents
'Multiply the dates by 1 to force a format update
End Sub
回答by Taptronic
I struggled to get this to work too. My problem has been not just dates but also data with a single quote in front of it. What I hacked together works great for me. It cleans up over 70,000 cells very fast. Hope it works for you:
我也努力让这个工作。我的问题不仅仅是日期，还有前面带有单引号的数据。我一起破解的东西对我很有用。它可以非常快速地清理超过 70,000 个细胞。希望这对你有用：
(you will change the range and such to suit your needs)
（您将更改范围等以满足您的需求）
Dim MyRange As Range
Set MyRange = Range(Cells(2, 7), [G1].End(xlDown))
For Each MyRange In MyRange.Cells
'Mimic F2 without SendKeys
MyRange.Value = MyRange.Value
Next
回答by RafaEsp
This worked for me.
这对我有用。
Dim r As Range
Dim n As Integer
Dim AssDateLastRow As Long
AssDateLastRow = ActiveSheet.Range("E" & Rows.Count).End(xlUp).Row
Set r = Range("E2:E" & AssDateLastRow)
r.Select
r.NumberFormat = "ddmmyyyy;@"
r.Select
For n = 1 To r.Rows.Count
SendKeys "{F2}", True
SendKeys "{ENTER}", True
Next n
回答by anefeletos
My variation
我的变种
n = Selection.Rows.count
Dim r1 As range, rv As range
Set r1 = Selection.Cells(1, 1)
For I = 1 To n
Set rv = r1.offset(I - 1, 0)
vali = rv.value
IsNumeric(vali) Then
vali = CDbl(vali)
rv.value = 0
rv.value = vali
End If
回答by user6462924
Try to press F9 or File-Option-formulas-workbook calculation- automatic
尝试按F9或File-Option-formulas-workbook计算-自动
回答by Bill LaPrise
I just set the cell to the right of the top entry equal to a formula that multiplied the problem cell times 1. That new cell was a proper number, so then double clicking the handle extended it down the whole column fixed them all!
我只是将顶部条目右侧的单元格设置为等于将问题单元格乘以 1 的公式。该新单元格是一个正确的数字，因此双击手柄将其向下扩展到整个列，将它们全部修复！
回答by Jeet Patel
Sub RefreshCells()
Dim r As Range, rr As Range
Set rr = Selection
For Each r In rr
r.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
Application.SendKeys "+{ENTER}"
DoEvents
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.SendKeys "{ENTER}"
DoEvents
Next
End Sub
回答by Jayanth
Sendkeys are not stable. The better way is to store the text in the clipboard and paste it.
发送键不稳定。更好的方法是将文本存储在剪贴板中并粘贴。
See here on how to store values in the clipboard
Sub CopyText(Text As String)
Dim MSForms_DataObject As Object
Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
MSForms_DataObject.SetText Text
MSForms_DataObject.PutInClipboard
Set MSForms_DataObject = Nothing
End Sub
Sub Test()
CopyText (ActiveCell.Value)
ActiveCell.PasteSpecial
End Sub
'In place of active cell, you may pass a range
回答by MatthewHagemann
It seems odd that you would need to send keys F2 + Enter. What is the formatting before you run the macro? Try formatting the whole column that way (it won't affect the text).
您需要发送 F2 + Enter 键似乎很奇怪。运行宏之前的格式是什么？尝试以这种方式格式化整个列（它不会影响文本）。
Columns("C:C").NumberFormat = "yyyy-mm-dd"
