问题描述
我在Excel中有如下数据(此处为一行-Excel中为一个单元格):
I have data in Excel like follows (one row here - one cell in Excel):
07 July 2015 12:02 – 14 July 2015 17:02
12 August 2015 22:02 – 01 September 2015 11:02
我想编写一个宏,该宏将删除用户选择的内容(多个单元格)中的所有时间信息(例如"12:02"),如下所示:
I want to write a macro that will delete all time info (e.g. "12:02") within a user's selection (multiple cells) to look like this:
07 July 2015 – 14 July 2015
12 August 2015 – 01 September 2015
当所有时间"都相似("00:00")时,此宏运行良好:
When all "times" where similar ("00:00") this macro worked perfectly:
Sub delete_time()
Selection.Replace What:="00:00", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
但是随后time-info不再统一,所以我决定使用RegEx.问题是我找不到在VBA上执行此操作的正确方法.我尝试了这个宏:
But then time-info stopped being uniform, so I decided to use RegEx. The problem is I can't find a proper way to do this on VBA. I tried this macro:
Sub delete_time()
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
On Error Resume Next
RegEx.Global = True
RegEx.Pattern = "\d\d\:\d\d"
ActiveDocument.Range = _
RegEx.Replace(ActiveDocument.Range, "")
End Sub
但是没有用.还尝试了"[0-9] {2}:[0-9] {2}"和"[0-9] [0-9]:[0-9] [0-9]"模式,但没有任何变化.因此,问题一定出在我对VBA的误解(我是新手).
But it didn't work. Also tried "[0-9]{2}:[0-9]{2}" and "[0-9][0-9]:[0-9][0-9]" patterns but nothing changed. So the problem must be in my misunderstanding of VBA (I'm new to it).
任何人都可以帮忙吗?
推荐答案
问题出在您的选择上.
ActiveDocument.Range = _
RegEx.Replace(ActiveDocument.Range, "")
ActiveDocument在Excel名称空间中不存在.我们有ActiveWorkbook或ThisWorkbook,但是现在您需要的是Selection.
ActiveDocument doesn't exist in the Excel namespace. We have ActiveWorkbook or ThisWorkbook, but what you need now is the Selection.
对每个循环使用a来迭代当前选择中的所有单元格,如下所示:
Use a for each loop to iterate all the cells in the current selection like this:
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Value = RegEx.Replace(myCell.Value, "")
Next
这篇关于在Excel-VBA中使用RegEx替换文本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!