辨別程式

各位前輩你們好!
               前輩!!問題如附檔說明
               Sub 找最後一筆()
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = ActiveWindow.ScrollColumn + [A9]
[B8] = [A8]: [B6] = 495: [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
[B65536].End(xlUp)(2, 1) = ActiveCell
[B7] = 0: Calculate
End Sub
            請知道的前輩,不吝賜教謝謝再三!!

LeoV59.rar (18.61 KB)

樓主熱帖

register313

　頭銜: 麻辣路人甲

大學生 Rank: 4

帖子: 967
主題: 0
精華: 0
積分: 1001
點名: 0
作業系統: WIN XP
軟體版本: OFFICE 2003
閱讀權限: 50
性別: 男
來自: 台北
註冊時間: 2010-11-29
最後登錄: 2022-5-17

2012龍年全勤獎章熱烈參與勳章

2^#

發表於 2012-3-18 19:41 | 只看該作者

回復 1# myleoyes

不是很明白你要的完整功能,試試看

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) = "A9" And [B28] = "" Then
找最後一筆
End If
End Sub

複製代碼

Sub 找最後一筆()
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = ActiveWindow.ScrollColumn + [A9]
[B8] = [A8]: [B6] = 495: [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
[B28] = ActiveCell
[B7] = 0: Calculate
End Sub

複製代碼

TOP

myleoyes

高中生 Rank: 3 Rank: 3

帖子: 463
主題: 116
精華: 0
積分: 580
點名: 0
作業系統: Vista
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-11-13

論壇贊助勳章論壇榮譽勳章

3^#

發表於 2012-3-19 22:12 | 只看該作者

回復 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
不好意思辛苦囉!謝謝再三!!

LeoV59-1.rar (19.59 KB)