今天VBA项目甲方反馈有一些问题,其中有一个是:
将EXCEL表格中一个单元格中的:
TX121 MF501
TX122 MF501
TX123 MF501
TX124 MF501
TX125 MF501
拆分成5个,每个数据都是以T开头
之前我的代码是这样写的:
'热敏电阻
If X1 = 1 Then '有热敏电阻的情况下
Thermistor = Split(DataSheet.Cells(Pointer, 25), vbLf) '分割热敏电阻并放到数组里面
For i = 0 To UBound(Thermistor)
CopySheet.Cells(ListPointer, 5) = "热敏电阻"
CopySheet.Cells(ListPointer, 6) = Thermistor(i)
CopySheet.Cells(ListPointer, 9) = 1
ListPointer = ListPointer + 1
Next
End If
后来发现不行,单元格中的数据并没有想象中的这么好,能够用vbLf分开,于是我想到了Replace函数,但是仅仅用Replace函数也不行,因为开头的也变了,于是我又想起来了Mid
'热敏电阻
If X1 = 1 Then '有热敏电阻的情况下
myContent = DataSheet.Cells(Pointer, 25)
myContent = Replace(myContent, "T", ",T")
myContent = Mid(myContent, 2)
Thermistor = Split(myContent, ",") '分割热敏电阻并放到数组里面
For i = 0 To UBound(Thermistor)
CopySheet.Cells(ListPointer, 5) = "热敏电阻"
CopySheet.Cells(ListPointer, 6) = Thermistor(i)
CopySheet.Cells(ListPointer, 9) = 1
ListPointer = ListPointer + 1
Next
End If
结果很完美!