VBA相关

Sub test()
Dim newnameindex
newnameindex = 6085

For i = 0 To 6082

Dim names
Dim namearray
names = Cells(i, 5)

If (InStr(1, names, "；") > 0) Then

namearray = Split(names, "；")

For j = 0 To UBound(namearray)
newnameindex = newnameindex + 1
Cells(newnameindex, 5) = namearray(j)
Next j
Cells(i, 5) = ""
End If

Next i

End Sub

 

Sub test2()

For i = 1 To 687
Dim cellstring
Dim chemname
Dim package
Dim packagearray
cellstring = Cells(i, 3)
If (InStr(cellstring, "\") > 0) Then
chemname = Left(cellstring, (InStr(cellstring, "\") - 1))
package = Right(cellstring, Len(cellstring) - InStr(cellstring, "\"))
packagearray = Split(package, " ")
For j = 0 To UBound(packagearray)
Cells(i, j + 26) = packagearray(j)
Next j
If j > 0 And InStr(packagearray(j - 1), "CAS") > 0 Then
Cells(i, j + 28) = Left(packagearray(j - 1), InStr(packagearray(j - 1), "CAS") + 2)
Cells(i, j + 29) = Right(packagearray(j - 1), Len(packagearray(j - 1)) - InStr(packagearray(j - 1), "CAS") - 2)
End If
Else
chenmame = ""
package = ""
End If
Cells(i, 23) = chemname
Cells(i, 24) = package
Next i

End Sub

 

 

Sub test()

i = 2
Rem:从第二行开始执行

While Sheets("SAP导出表格").Cells(i, 12).Value <> ""
Rem:当“基本计量”字段为空的时候，认为已经读取完了所有数据行，停止执行

If (Sheets("SAP导出表格").Cells(i, 12).Value <> "千克") Then
Rem:如果单位为千克，单独处理

Cells(i, 1) = Sheets("SAP导出表格").Cells(i, 15).Value

Cells(i, 2) = Sheets("SAP导出表格").Cells(i, 2).Value

Cells(i, 4) = Sheets("SAP导出表格").Cells(i, 3).Value

Cells(i, 10) = Abs(Int(Sheets("SAP导出表格").Cells(i, 10).Value))

Cells(i, 13) = Sheets("SAP导出表格").Cells(i, 12).Value
Rem:开始拆分型号纯度及最小包装单位数据

package = Sheets("SAP导出表格").Cells(i, 3).Value

packagearray = Split(package, " ")

If UBound(packagearray) = 1 Then

Cells(i, 5) = packagearray(1)

ElseIf UBound(packagearray) = 2 Then

Cells(i, 5) = packagearray(1) + " " + packagearray(2)

minipacakge = packagearray(2)

s = 1

For s = 1 To Len(minipacakge)

char = Mid(minipacakge, s, 1)

If (Not (IsNumeric(char))) Then

Cells(i, 11) = Mid(minipacakge, 1, s - 1)

Cells(i, 12) = Mid(minipacakge, s, Len(minipacakge) - s + 1)

Exit For

End If

Next s

End If

Else
Rem:如果单位不是千克，拆分型号和最小计量及最小剂量的单位

Cells(i, 1) = Sheets("SAP导出表格").Cells(i, 15).Value

Cells(i, 2) = Sheets("SAP导出表格").Cells(i, 2).Value

Cells(i, 4) = Sheets("SAP导出表格").Cells(i, 3).Value

Cells(i, 10) = Abs(Int(Sheets("SAP导出表格").Cells(i, 10).Value))

Cells(i, 13) = Sheets("SAP导出表格").Cells(i, 12).Value

End If

i = i + 1

Wend

End Sub