- 帖子
- 2834
- 主題
- 10
- 精華
- 0
- 積分
- 2890
- 點名
- 0
- 作業系統
- 〔略〕
- 軟體版本
- 〔略〕
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 〔略〕
- 註冊時間
- 2013-5-13
- 最後登錄
- 2024-11-26
|
67#
發表於 2018-10-16 11:56
| 只看該作者
稍改
Sub Trans_Qty()
Dim R&
With Sheets("Qty on Hand")
.AutoFilterMode = False
.UsedRange.Offset(1, 0).EntireRow.Delete
End With
R = Cells(Rows.Count, 1).End(xlUp).Row - 7
If R <= 0 Then Exit Sub
With ['Qty on Hand'!A2:I2].Resize(R)
[A8:I8].Resize(R).Copy .Cells
.Sort Key1:=.Item(6), Order1:=xlAscending, _
Key2:=.Item(3), Order1:=xlAscending, Header:=xlNo
End With
['Qty on Hand'!A1:I1].Resize(R + 1).AutoFilter
With ['Qty on Hand'!I2].Resize(R)
.NumberFormatLocal = "#,##0;-#,##0"
'.Formula = "=IF(F2=F3,""A"","""")&TEXT(MID(I1,2,99),""0;-0;0;!0"")+N(H2)" '公式(1)
'.Formula = "=IF(F2=F3,""A"","""")&IF(ROW(A1)=1,0,MID(I1,2,99))+N(H2)" '公式(2)
.Formula = "=IF(F2=F3,"""",SUMIF(F:F,F2,H:H))" '公式(3)
'三種公式任選一個, 資料多, 看哪個快, 選哪個
.Value = .Value
.Replace "A*", "", Lookat:=xlPart '使用公式(3), 可省略這一行
End With
Application.Goto ['Qty on Hand'!A2]
End Sub |
|
發表於 2018-10-17 23:43
| 
