本帖最后由 苗凱 于 2014-2-19 17:09 编辑
Sub 按钮1_Click()
Dim arr, brr, regex As Object, flag As Boolean, tmp As String
Dim DocNumber As String, mat_descr As String, Verdor_No As String
Dim qt As Long, cdt As Single, amount As Long, unit As Single, sap As Integer
Sheet3.Range("b2").CurrentRegion.Offset(1).ClearContents '清空 结果数据 表的已有数据
flag = False
Set regex = CreateObject("vbscript.regexp") '创建正则表达式
regex.Pattern = "^\d+?0 " '规则为以数字开头的至少一位数字,碰到0时截止
arr = [a1].CurrentRegion 'A1的当前区域放入数组ARR
ReDim brr(1 To UBound(arr), 1 To 7) '重新声明BRR,1到ARR的上标,1到7
For i = 1 To UBound(arr) 'I等于 1到 ARR的上标
If arr(i, 1) Like "Document Number*" Then ' 如果数组的I行1列的值匹配到是以Document Number开头的话
DocNumber = Replace(arr(i, 1), "Document Number ", "") '用ARR的值替代为Document Number
End If
If regex.test(arr(i, 1)) Then '截取物料所在行的数据
n = n + 1
crr = Split(Application.Trim(arr(i, 1)), " ") '去掉ARR数组的前后空格然后分离
sap = crr(0) 'CRR的第一个值保存在SAP中
mat_descr = crr(1) 'CRR的第2个值保存在mat_descr中
qt = crr(2) 'CRR的第3个值保存在qt 中
cdt = crr(4) 'CRR的第5个值保存在cdt 中
unit = crr(6) 'CRR的第7个值保存在unit中
amount = crr(8) 'CRR的第9个值保存在amount中
End If
If arr(i, 1) Like "Vendor Material No*" Then '截取客户料号所在行数据
Verdor_No = Replace(arr(i, 1), "Vendor Material No.: ", "")
flag = True
End If
If n > 0 Then
If brr(n, 1) = "" Then brr(n, 1) = mat_descr '物料号
If brr(n, 2) = "" Then brr(n, 2) = Verdor_No '客户料号
If brr(n, 1) <> "" And brr(n, 2) <> "" And flag Then
tmp = brr(n, 1)
brr(n, 1) = brr(n, 2)
brr(n, 2) = tmp
End If
flag = False
If brr(n, 3) = "" Then brr(n, 3) = qt '数量
If brr(n, 4) = "" Then brr(n, 4) = Round(cdt / unit * 1.17, 5) '未税单价 乘 1.17
If brr(n, 5) = "" Then brr(n, 5) = Round(cdt / unit * qt, 2) '未税金额
If brr(n, 6) = "" Then brr(n, 6) = sap
If brr(n, 7) = "" Then brr(n, 7) = DocNumber
Verdor_No = ""
End If
Next
Sheet3.[a2].Resize(UBound(brr), 7) = brr
MsgBox "Completed !", vbInformation + vbOKOnly, "Congratulations"
End Sub
|