Board logo

標題: 辨別程式 [打印本頁]
作者: myleoyes    時間: 2012-3-17 23:20     標題: 辨別程式

各位前輩你們好!
                 前輩!!問題如附檔說明
                 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
                請知道的前輩,不吝賜教謝謝再三!!
作者: register313    時間: 2012-3-18 19:41

回復 1# myleoyes

不是很明白你要的完整功能,試試看
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.   If Target.Address(0, 0) = "A9" And [B28] = "" Then
  3.      找最後一筆
  4.   End If
  5. End Sub
複製代碼
  1. Sub 找最後一筆()
  2.     ActiveWindow.ScrollColumn = 1
  3.     ActiveWindow.ScrollColumn = ActiveWindow.ScrollColumn + [A9]
  4.     [B8] = [A8]: [B6] = 495: [A12:B12] = 0
  5.     Set a = [A8]
  6.     Set c = [C1:IV1].Find(a, , , 1)
  7.     If Not c Is Nothing Then c.Select
  8.     ActiveCell.Offset(0, 4).Font.ColorIndex = 10
  9.     ActiveCell.Offset(0, 4).Font.Size = 9
  10.     ActiveCell.Offset(0, 5).Font.ColorIndex = 30
  11.     ActiveCell.Offset(0, 5).Font.Size = 9
  12.     ActiveCell.Offset(0, 6).Font.ColorIndex = 46
  13.     ActiveCell.Offset(0, 6).Font.Size = 9
  14.     ActiveCell.Offset(0, 8).Font.ColorIndex = 5
  15.     ActiveCell.Offset(0, 8).Font.Size = 9
  16.     c.Offset(0, 8).Select
  17.     [B28] = ActiveCell
  18.     [B7] = 0: Calculate
  19. End Sub
複製代碼
作者: myleoyes    時間: 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
不好意思辛苦囉!謝謝再三!!
作者: register313    時間: 2012-3-20 13:12

回復 3# myleoyes

Sub yy()  可簡化如下
  1. Sub yy()
  2. For C = 6 To 26 Step 2
  3.   If Cells(9, C) <> "" Then Cells(10, C) = Cells(7, C) / Cells(3, C)
  4. Next C
  5. End Sub
複製代碼
Sub 格式化()  我就不懂了
作者: myleoyes    時間: 2012-3-20 21:23

回復 4# register313
前輩!!辛苦囉!謝謝!!
        格式化程式是用錄製的方式
        所以一大串小弟想應有精簡的寫法才對
       謝謝再三!!



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)