|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
换个不用正则的方法(其实是我不会),另外增加了单位
Private Sub CommandButton1_Click()
Dim iArr_Data()
With Sheet2
.Range("E2:XFD1048576").ClearContents
iRow_Data = .Range("D1048576").End(xlUp).Row
iArr_Data() = .Range("D2:D" & iRow_Data)
niArr_Data = iRow_Data - 1
For i = 1 To niArr_Data
y = 0
myQty = Empty
myName = iArr_Data(i, 1)
k = Len(myName)
For j = 1 To k
myWord = Mid(myName, j, 1)
If IsNumeric(myWord) = True Or myWord = "." Then
If LCase(Mid(myName, j + 1, 2)) = "kg" Or LCase(Mid(myName, j + 1, 2)) = "ml" Or LCase(Mid(myName, j + 1, 1)) = "g" Then
myQty = myQty & myWord
If LCase(Mid(myName, j + 1, 2)) = "kg" Then
.Cells(i + 1, 5 + y * 2).Value2 = myQty * 1000
.Cells(i + 1, 6 + y * 2).Value2 = "g"
End If
If LCase(Mid(myName, j + 1, 2)) = "ml" Then
.Cells(i + 1, 5 + y * 2).Value2 = myQty * 1
.Cells(i + 1, 6 + y * 2).Value2 = "ml"
End If
If LCase(Mid(myName, j + 1, 1)) = "g" Then
.Cells(i + 1, 5 + y * 2).Value2 = myQty * 1
.Cells(i + 1, 6 + y * 2).Value2 = "g"
End If
y = y + 1
myQty = Empty
Else
myQty = myQty & myWord
If IsNumeric(myQty) = False Then myQty = Empty
End If
Else
myQty = Empty
End If
Next
Next
End With
Erase iArr_Data()
End Sub
|
|