- 帖子
- 463
- 主題
- 116
- 精華
- 0
- 積分
- 580
- 點名
- 0
- 作業系統
- Vista
- 軟體版本
- 2007
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2010-5-4
- 最後登錄
- 2017-11-13
 
|
回復 2# register313
前輩!!非常謝謝你..!這是檔案自動化的程序之一,用手動方式來表示
前輩!當然比較難以理解,程式已找到沒辦法對於Execl
的認知有限,只能用如此笨拙的方式表現請勿見笑
附檔請你看看
Sub 找最後一筆()
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = ActiveWindow.ScrollColumn + [A9]
[B6] = "=IF(A8=0,0,A8*15-15)": [B6] = [B6]: [B8] = [A8]: [A12:B12] = 0 '此行為定位儲存格
Set a = [A8]
Set c = [C1:IV1].Find(a, , , 1)
If Not c Is Nothing Then c.Select
ActiveCell.Offset(0, 4).Font.ColorIndex = 10
ActiveCell.Offset(0, 4).Font.Size = 9
ActiveCell.Offset(0, 5).Font.ColorIndex = 30
ActiveCell.Offset(0, 5).Font.Size = 9
ActiveCell.Offset(0, 6).Font.ColorIndex = 46
ActiveCell.Offset(0, 6).Font.Size = 9
ActiveCell.Offset(0, 8).Font.ColorIndex = 5
ActiveCell.Offset(0, 8).Font.Size = 9
c.Offset(0, 8).Select
[A10] = ActiveCell
Set c = [B21:B520].Find([A10], , , 1)
If Not c Is Nothing Then c.Select
If Not c Is Nothing Then [B10] = ActiveCell
If [B10] <> "" Then [A10:B10] = "": Calculate: Exit Sub
If [B10] = "" Then [B520].End(xlUp)(2, 1) = ActiveCell
[A10:B10] = "": Calculate '此行為補助儲存格
[B7] = 0: Calculate
End Sub
可否幫小弟簡化這兩個程式嗎?
Sub yy()
If [F9] <> "" Then [F10] = "=F7/F3"
If [H9] <> "" Then [H10] = "=H7/H3"
If [J9] <> "" Then [J10] = "=J7/J3"
If [L9] <> "" Then [L10] = "=L7/L3"
If [N9] <> "" Then [N10] = "=N7/N3"
If [P9] <> "" Then [P10] = "=P7/P3"
If [R9] <> "" Then [R10] = "=R7/R3"
If [T9] <> "" Then [T10] = "=T7/T3"
If [V9] <> "" Then [V10] = "=V7/V3"
If [X9] <> "" Then [X10] = "=X7/X3"
If [Z9] <> "" Then [Z10] = "=Z7/Z3"
End Sub
Sub 格式化()
[M3:M152].Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I$1=""計息"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16744448
.TintAndShade = 0
End With
[I3:I152].Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I$1=""計息"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -65281
.TintAndShade = 0
End With
[H3:H152].Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=I$1=""計息"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -65536
.TintAndShade = 0
End With
Application.CutCopyMode = False
End Sub
不好意思辛苦囉!謝謝再三!! |
|
發表於 2012-3-17 23:20
| 
