willy1205

¦^´_ 38# willy1205

excercise.zip (64.56 KB)

¤U¸ü¦¸¼Æ: 7

2014-5-22 17:30

¦p½m²ß³o­ÓÀɮסA­ì¥»¬O§PÂ_B10»PB12(A§g»PB§g)µM«á«ö¤U«ö¶s¶×¤J¸ê®Æ¶i¥hA§g»PB§gªºSHEET¡A
²{¦b·Q¦bC10»PC12¡AD10»PD12¤À§O¥[¤JC§g D§g F§g E§gªº¸Ü¡A¸Ó«ç»ò´¡¤J¶i¥h§PÂ_¦¡©O??

willy1205

¦pªG±N­ì¥»ªº
For Each E In Sh.Range("B10,B12")
If E.Address = "$B$10" Then
            NewRow = Sh.Range("J34").Value
        Else
            NewRow = Sh.Range("J35").Value
        End If
§ï¦¨
For Each E In Range("B10,B12,C10,C12")
    Select Case E.Address(0, 0)
    Case "B10"
        NewRow = 1
    Case "B12"
        NewRow = 2
    Case "C10"
        NewRow = 3
   Case "C12"
NewRow = 4
    End Select
¤U­±ªºWith Worksheets(E.Value)·|Åܦ¨out of range

¸Ó¦p¦ó­×§ï?

willy1205

¦^´_ 36# GBKEE


   ª©¥DÁÂÁ§A¡A½Ð°Ý¦pªG­n§â¥L¼g¦b¦P¤@­Ó«ö¶s¥¨¶°¸Ì­±³o¦³¿ìªk¶Ü??

GBKEE

¦^´_ 35# willy1205

1. Sub Ex()
   
2. Dim E, NewRow
   
3. For Each E In Range("B8,B10,C10,C12")
   
4.     Select Case E.Address(0, 0)
   
5.     Case "B8"
   
6.         NewRow = 1
   
7.     Case "B10"
   
8.         NewRow = 2
   
9.     Case "C10"
   
10.         NewRow = 3
    
11.     Case "C12"
    
12.         NewRow = 4
    
13.     End Select
    
14. 
    
15.     MsgBox NewRow
    
16. Next
    
17. End Sub
    
18. Sub Ex1()
    
19. Dim E, NewRow
    
20. For Each E In Range("B8,B10,C10,C12")
    
21.     If E.Address(0, 0) = "B8" Then
    
22.         NewRow = 1
    
23.     ElseIf E.Address(0, 0) = "B10" Then
    
24.         NewRow = 2
    
25.     ElseIf E.Address(0, 0) = "C10" Then
    
26.         NewRow = 3
    
27.     ElseIf E.Address(0, 0) = "C12" Then
    
28.         NewRow = 4
    
29.     End If
    
30.     MsgBox NewRow
    
31. Next
    
32. End Sub

½Æ»s¥N½X

willy1205

ª©¥D¦³¤èªk¥i¥H ¸Ñ¨M¶Ü!?

willy1205

¦^´_ 32# willy1205


    ¸Õ¤F¤@¤U¦n¹³¨S¿ìªkª½±µ¥[¤JC10 C12,¥B¤U­±§PÂ_¦¡·|¥d¦º...

willy1205

¦^´_ 29# GBKEE


   ª©¥D§Ú³Ìªñ­«·s½m²ß¤Sµo²{¤F¤@­Ó°ÝÃD¡A´N¬O¦pªG§Ú·Q­n¦h§PÂ_C10 »P C12 ,¤]´N¬O¦P®É¥i¯à¨â­Ó¥D¿ì¨â­Ó¨ó¿ì¡A
¯à¦b'For Each E In Sh.Range("B10,B12")' ³oÃ䪽±µ´¡¤J'For Each E In Sh.Range("B10,B12,C10,C12") ¶Ü??'
¨º§Ú¦b¤U­±IF§PÂ_¦¡¸Ó¦p¦ó­×§ï??
Åã¥Üªº¦ì¤l³£¤£ÅÜ¥u¬O¦h§PÂ_Àx¦s®æC10»PC12

opelwang

¦^´_ 26# opelwang


    À³¸Ó¬O¥´¿ù¦r¤F, ¥¿½T¬O:

     ¾Ç²ßVBAªº¥Îªk.

willy1205

¦^´_ 29# GBKEE


    ¤F¸Ñ ÁÂÁª©¥D

GBKEE

¦^´_ 28# willy1205

1. Option Explicit
   
2. Sub RoundedRectangle1_Click()
   
3.     Dim NewRow As Integer, E As Range, Sh As Worksheet
   
4.     Set Sh = Worksheets("Sheet1")  'Åܼƫü©w¬°¤u§@ªí **¤U­±ªºµ{¦¡½X¦p¦³§ó§ï ¤u§@ªí **§ï³oÅܼƥi¬Ù³Â·Ð
   
5.    'Worksheets("sheet1").Range("B8").Value  'ºÞ¥L¬O ¥Ò§g,¤A§g,¤þ§g ¥u­n³o¤u§@ªí(¦WºÙ)¦³¦b¬¡­¶Ã¯¤¤,
   
6.     For Each E In Sh.Range("B8,B10")  '¾É¤JB8,B10 ªºÀx¦s®æ
   
7.         '§PÂ_ ¥D¿ì,¨ó¿ì ªº¦C¸¹
   
8.         If E.Address = "$B$8" Then   '¥D¿ì
   
9.             NewRow = Sh.Range("J34").Value
   
10.         Else                         '¨ó¿ì
    
11.             NewRow = Sh.Range("J35").Value
    
12.         End If
    
13.         
    
14.         With Worksheets(E.Value)   'E.Value ªº¤u§@ªí
    
15.             .Cells(NewRow, 1) = Sh.Range("C5")
    
16.             .Cells(NewRow, 2) = Sh.Range("C6").Value
    
17.             .Cells(NewRow, 3) = Sh.Range("I12").Value
    
18.             .Cells(NewRow, 4) = Sh.Range("I13").Value
    
19.             .Cells(NewRow, 6) = Sh.Range("G22").Value
    
20.             '§PÂ_¥D¿ì,¨ó¿ì ­n§ïÅܪº¦ì¸m¦C¸¹
    
21.             If E.Address = "$B$8" Then   '¥D¿ì
    
22.                 .Cells(NewRow, 7) = Sh.Range("H30")
    
23.             Else                          '¨ó¿ì
    
24.                 .Cells(NewRow, 7) = Sh.Range("H32")
    
25.             End If
    
26.         End With
    
27.     Next
    
28.     MsgBox "New Data added", vbOKOnly, "Data"
    
29. End Sub

½Æ»s¥N½X