以下是引用amulee在2009-12-22 11:33:00的发言: 老办法,稍作修改 Sub Test() On Error Resume Next Dim ArrD, ArrYS, i& Dim RegEx As Object, MyA As Object, sTemp Dim d As Object Set d = CreateObject("Scripting.Dictionary") '建立字典 ArrD = Sheet2.Range("B2:C" & Sheet2.Range("A65536").End(xlUp).Row) For i = 1 To UBound(ArrD, 1) d(ArrD(i, 1)) = IIf(ArrD(i, 2) = "A", 2, IIf(ArrD(i, 2) = "B", 3, 4)) Next i '建立正则表达式对象 Set RegEx = CreateObject("VBScript.RegExp") 'RegEx为建立正则表达式 RegEx.Global = True '原始数组 ArrYS = Sheet1.Range("A2:D" & Sheet1.Range("A65536").End(xlUp).Row) For i = 1 To UBound(ArrYS, 1) RegEx.Pattern = Join(d.keys, "|") Set MyA = RegEx.Execute(ArrYS(i, 1)) For Each sTemp In MyA ArrYS(i, 1) = RegEx.Replace(ArrYS(i, 1), "") ArrYS(i, d(CStr(sTemp))) = sTemp Next sTemp '多余的逗号 RegEx.Pattern = ",," ArrYS(i, 1) = RegEx.Replace(ArrYS(i, 1), ",") RegEx.Pattern = "^,|,$" ArrYS(i, 1) = RegEx.Replace(ArrYS(i, 1), "") Next i Sheet1.Range("A2").Resize(UBound(ArrYS, 1), UBound(ArrYS, 2)) = ArrYS Set RegEx = Nothing Set d = Nothing End Sub 呵呵,这个刚才用数据测试通过了,我刚才没把问题描述清楚,这样的,要提取的单元格里,往往不止一个提取元素,比如A1中,电视机和电脑都属于B类别,符合提取条件,都得提取,所以提前出来后,放在一个单元格里,用符号隔开,如 电视机,电脑。 [
[此贴子已经被作者于2009-12-23 9:11:43编辑过] |