[µo°Ý] EXCEL VBA ¦p¦ó±N­«½Æªºµ{¦¡¦X¨Ö

ken2192

¦U¦ìª©¤Í¦n¡A
¥H¤U¬O§Ú¦bexcel vba¤¤¡A¬Y¤@ÀɪѲ¼¬Y±ø¥ó¦¨¥ß¤§«á¡A¦Û°Ê¦b©T©wÀx¦s®æ¸Ì²Ö¥[¦¸¼Æ¡A¦ýexcel¤¤·|¦³50ÀɪºªÑ²¼¡A©ó¬O§Ú¥u¯à½Æ»s¶K¤W¤@¼Ëªº«ü¥ß¡A¨Ã­×§ïM9->M10¡C
¦ý¥Ñ©ó¤ÓÁcº¾¥B­nÀ³¥Î¦b9-50¦C¡A½Ð°Ýª©¤Í§Ú¦p¦ó±N­«½Æªº«ü¥O¦X¨Ö¦b¦P¤@­Óµ{¦¡¸Ì¡A¨CÀɪѲ¼¦¨³æ¿W¨Æ¥ó¦Û°Ê¥h²Ö¥[!!


Private Sub Worksheet_Change(ByVal Target As Range)
Call script1(Target)
Call script2(Target)
Call script3(Target)
Call script4(Target)
End Sub
--------------------------------------------------------------
Private Sub script1(ByVal Target As Range)
If Target.Cells.Address = "$M$9" Then
If [M9] >= [P9] * 0.01 Then
[L9] = [M9] / [M9] + [L9]
End If
End If
End Sub
------------------------------------------------------------------
Private Sub script2(ByVal Target As Range)
If Target.Cells.Address = "$M$10" Then
If [M10] >= [P10] * 0.01 Then
[L10] = [M10] / [M10] + [L10]
End If
End If
End Sub
--------------------------------------------------------------------
Private Sub script3(ByVal Target As Range)
If Target.Cells.Address = "$M$11" Then
If [M11] >= [P11] * 0.01 Then
[L11] = [M11] / [M11] + [L11]
End If
End If
End Sub
---------------------------------------------------------------------
Private Sub script4(ByVal Target As Range)
If Target.Cells.Address = "$M$12" Then
If [M12] >= [P12] * 0.01 Then
[L12] = [M12] / [M12] + [L12]
End If
End If
End Sub

GBKEE

¦^´_ 1# ken2192

1. Option Explicit
   
2. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
3.     Dim Rng As Range
   
4.     Set Rng = [m9:m50] '**M9,M10,,M49,M50 ¡C ­nÀ³¥Î¦b9-50¦C
   
5.     If Not Intersect(Rng, Target) Is Nothing Then
   
6.     '**Intersect ¤èªk ¨Ò¯S©w¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí¨â­Ó©Î¦h­Ó½d³ò­«Å|ªº¯x§Î½d³ò¡C
   
7.     '**expression.Intersect(Arg1, Arg2, ...)
   
8.         With Target  '** Target¬O[m9:m50]¤¤ªºÀx¦s®æ
   
9.             If IsNumeric(.Cells) And .Cells > .Cells(1, 4) * 0.01 Then .Cells(, 0) = .Cells(, 0) + 1
   
10.             '**Cells(1, 4) ---¥HMÄ欰°ò·Ç-> 4=PÄæ,
    
11.             '**.Cells(, 0) => .Cells(1, 0) ---¥HMÄ欰°ò·Ç->0=LÄæ,
    
12.         End With
    
13.     End If
    
14. End Sub

½Æ»s¥N½X

ken2192

If IsNumeric(.Cells) And .Cells >= .Cells(1, 4) * 0.01 Then

¦¹¼gªk¥X²{°»¿ù°T®§¡AµLªk¶¶§Q¶]¥X!!¥i§_½Ðª©¥D¦bÀ°§Ú¹L¥Ø¤@¤U