|
将就着用吧!模板请不要改动,数据源务必放在模板后面,即:从左数到右第2个工作表,后面不要再添加任何无关的工作表了,因为代码会自动删掉之后的工作表。{:1612:}
调格式太累了,感觉数据还不够典型,就这样吧!{:2712:}
- Sub test()
- Dim ar, br, cr, dr(1 To 100, 1 To 1), er(1 To 100, 1 To 1)
- Dim i As Long, j As Long
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- ar = Sheets(2).Cells(1, 1).CurrentRegion
- For i = Sheets.Count To 3 Step -1
- Sheets(i).Delete
- Next i
- For i = 2 To UBound(ar)
- Sheets("模板").Copy , Sheets(Sheets.Count)
- With ActiveSheet
- .Cells(9, 2) = ar(i, 4)
- If IsEmpty(ar(i, 2)) Then
- .Cells(9, 9) = "'bsdq95" & ar(i, 1)
- Else
- .Cells(9, 9) = "'bsdq95" & ar(i, 1) & "-" & ar(i, 2)
- End If
- .Cells(14, 3) = ar(i, 10)
- .Cells(14, 9) = ar(i, 3)
- If IsEmpty(ar(i, 8)) Then
- br = Split(ar(i, 5), Chr(10))
- cr = Split(ar(i, 6), Chr(10))
- For j = 0 To UBound(br)
- dr(j + 1, 1) = br(j)
- er(j + 1, 1) = cr(j)
- Next j
- .Rows("19:" & 22 + j).Insert Shift:=xlDown
- With .Cells(19, 9)
- .Value = "HKD"
- .HorizontalAlignment = xlCenter
- .Font.Underline = xlUnderlineStyleDouble
- End With
- With .Cells(20, 1).Resize(j)
- .Value = dr
- .Interior.ColorIndex = 6
- End With
- .Cells(20, 9).Resize(j) = er
- With .Cells(21 + j, 9)
- .Value = "=SUM(I20:I" & 20 + j & ")"
- .Interior.ColorIndex = 6
- End With
- .Cells(20, 9).Resize(j + 2).Style = "Comma"
- Else
- .Rows("19:22").Insert Shift:=xlDown
- .Cells(21, 1) = "RE : "
- .Cells(21, 2) = ar(i, 4)
- .Cells(21, 1).Resize(, 2).Font.Bold = True
- With .Cells(22, 9)
- .Value = "USD"
- .HorizontalAlignment = xlCenter
- .Font.Underline = xlUnderlineStyleSingle
- End With
- br = Split(ar(i, 5), Chr(10))
- cr = Split(ar(i, 8), Chr(10))
- For j = 0 To UBound(br)
- dr(j + 1, 1) = br(j)
- er(j + 1, 1) = cr(j)
- Next j
- .Rows("24:" & 31 + j).Insert Shift:=xlDown
- .Cells(24, 1).Resize(j + 1) = dr
- With .Cells(24, 9)
- .Resize(j + 1) = er
- .Interior.ColorIndex = 6
- End With
- .Cells(27 + j, 7) = "Equivalent to CNY"
- .Cells(27 + j, 8) = "=I" & 27 + j & "*6.8"
- With .Cells(27 + j, 9)
- .Value = "=SUM(I24:I" & 26 + j & ")"
- .Font.Underline = xlUnderlineStyleDouble
- End With
- With .Cells(27 + j, 7).Resize(, 3)
- .Interior.ColorIndex = 6
- .Font.Bold = True
- End With
- .Cells(27 + j, 7).HorizontalAlignment = xlRight
- .Cells(28 + j, 9).Font.Underline = xlUnderlineStyleDouble
- With .Cells(29 + j, 1)
- .Value = "BY REMITTANCE :"
- .Font.Bold = True
- .Font.Underline = xlUnderlineStyleSingle
- End With
- .Cells(30 + j, 1) = "Additional bank charges CNY155.00"
- .Cells(30 + j, 7) = "Equivalent to CNY"
- .Cells(30 + j, 8) = "=H" & 27 + j & "+155"
- With .Cells(30 + j, 9)
- .Value = "=I" & 27 + j & "+20"
- .Font.Underline = xlUnderlineStyleDouble
- End With
- With .Cells(30 + j, 1).Resize(, 9)
- .Font.Bold = True
- End With
- .Cells(30 + j, 1).Interior.ColorIndex = 6
- .Cells(30 + j, 7).HorizontalAlignment = xlRight
- With .Cells(30 + j, 7).Resize(, 3)
- .Interior.ColorIndex = 6
- .Font.Bold = True
- End With
- .Cells(24, 9).Resize(j + 7).Style = "Comma"
- End If
- Erase dr
- Erase er
- End With
- Next i
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|