|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
改成这样子,A1单元格是AWB No.,从A2单元格取得Ctns,G.W.,Parcel这三个数值,A3及以下单元格是计算CBM体积的。现在不止三行的也可以计算了。附件不传了,你自己修改下。PS:下次提问题要尽可能提出可能会出现的情况,因为你的数据量太少,我以为就只有三行了。
Sub test()
Dim Str As String
Dim i As Long
Dim MyRng As Range
Dim Arr, Brr
Arr = [A1].CurrentRegion
If UBound(Arr) = 0 Then Exit Sub
[I2] = Arr(1, 1) 'I2单元格值为选择单元格的区域
Str = Arr(2, 1)
With CreateObject("VBSCRIPT.REGEXP")
.Global = True
.Pattern = "[a-zA-Z]"
Str = .Replace(Str, "")
End With
spt = Split(Str, "/")
i = 0: ctns = VBA.Trim(spt(i)): [J2] = ctns: [N2] = ctns 'J2和N2单元格值
i = i + 1: Parcel = VBA.Trim(spt(i)): [M2] = Parcel 'M2单元格值
i = i + 1: GW = VBA.Trim(spt(i)): [K2] = GW 'K2单元格值
For i = 3 To UBound(Arr)
A = Split(Arr(i, 1), "/")
B = Evaluate(A(0)) * A(1) / 1000000
CBM = (CBM + B)
Next
[L2] = CBM
[L2].NumberFormatLocal = "0.00_ "
[Q2] = [L2]
[Q2].NumberFormatLocal = "0.00_ "
End Sub
|
|