ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¸ê®Æ¿é¤J¨¾§b°ÝÃD½Ð±Ð

[µo°Ý] ¸ê®Æ¿é¤J¨¾§b°ÝÃD½Ð±Ð

DEAR ALL ¤j¤j
1.RANGE("C4:C25") »P RANGE("D4:D25") ¬°´£¨Ñ¸g¿ì¿é¤J¤§½d³ò
   1.1 ¨Ò  C4¿é¤JA    D4¿é¤J1 «h A1¬°KEY
           ·í  C5¿é¤JA ®ÉD5¤@©w­n¿é¤J1§_«h¨q¥X¨¾§b"RANGE("D5")»Ý¿é¤J1"§i¤§¸g¿ì.
           1.1.1 D6-D25 ¥u­n¦³¬Û¦P±¡§Î§¡¨q¥X¨¾§b
       ¨Ò  C10¿é¤JB   D10¿é¤J9 «h B9¬°KEY
           ·í  C11¿é¤JB ®ÉD11¤@©w­n¿é¤J9§_«h¨q¥X¨¾§b"RANGE("D11")»Ý¿é¤J9"§i¤§¸g¿ì.
           1.1.1 D11-D25 ¥u­n¦³¬Û¦P±¡§Î§¡¨q¥X¨¾§b
2.½Ð±Ð¦p¦ó®Ñ¼g VBA  ·Ð¤£§[½ç±Ð   THANKS
ù

¥»©«³Ì«á¥Ñ luhpro ©ó 2015-1-1 08:19 ½s¿è
DEAR ALL ¤j¤j
1.RANGE("C4:C25") »P RANGE("D425") ¬°´£¨Ñ¸g¿ì¿é¤J¤§½d³ò
   1.1 ¨Ò  C4¿é¤JA    D4¿é ...
rouber590324 µoªí©ó 2014-12-31 13:32

¤@¯ë­n§i¶D¨Ï¥ÎªÌ°T®§¨Ãµ¥«Ý¨ä¦^ÂÐ(©Î«ö"½T©w"¶s)ªº»Ý¨D,
¥i¥H¨Ï¥Î   Msgbox "­n§iª¾ªº°T®§"    §Y¥i.

¤£¹LÆ[¹î§A¤W¤èªº±Ô­z¤º®e,
§Úµo²{§A¦³¦h¦¸±j½Õ   "¤@©w­n¿é¤J..."   ­n¨D¨Ï¥ÎªÌ¬Ý¨ì°T®§«á¥²¶·°µªº°Ê§@.

¦Ó´«­Ó¨¤«×·Q,
­Y¤£¼vÅT¨Ï¥ÎªÌ¹ê»Ú§@·~ªº±¡§Î¤U,
§A¥i¥H¦Ò¼{ª½±µ¦bVBAµ{¦¡¤¤¥N´À¨Ï¥ÎªÌ°µ³o­Ó°Ê§@.

¨Ò¦p : ·í  C5¿é¤JA ®ÉD5¤@©w­n¿é¤J1
´N¥Î   If [C5] = "A" Then  [D5]=1  §Y¥i.

¥t¥~¦b§Aªº±Ô­z¤¤:
°²³] [C4][D4] = A1 ®É, ·í C5~C25  ¿é¤J A ®É,¸Ó¦Cªº Dx ´N­n¬° 1
°²³] [C10][D10] = B9 ®É, ·í C11~C25  ¿é¤J B ®É,¸Ó¦Cªº Dx ´N­n¬° 9

°ÝÃD¨Ó¤F, °²³]¨Ï¥ÎªÌ¾Þ§@¤W­z¨â¨BÆJ¤§«á -
1. CxDx ªº¹ïÀ³Ãö«Y¬O§_¥þ³¡(§Y²Ä 4 ¦C ~ ²Ä 25 ¦C)¾A¥Î, ÁÙ¬O¥u¬O©¹¤U¾A¥Î?)

2. ­Y¦b C5 ¿é¤J B ®É, D5 Åý¤£Åý¨Ï¥ÎªÌ§ï? ÁÙ¬O·Ó¼Ë¥u¯à¿é¤J 9 ?
   2.1. °²³]±µ¨ü¨Ï¥ÎªÌ­×§ï D5=6 , ¨º¤§«e¤w¸g¿é¤Jªº D10(¬Æ¦Ü¨ä¥L¬Û¦P±¡§Î) ¬O§_¤](³£)­n§ï¦¨ 6 ? ¤S¬O§_¥u¬O©¹¤U¾A¥Î?

   2.2 °²³]¤£±µ¨ü¨Ï¥ÎªÌ­×§ï, ¨º¸U¤@¬O¤§«e¨Ï¥ÎªÌ¿é¤J¿ù»~ªº¸Ü, ¨º¤S¬O§_´£¨Ñ­×¥¿ªº¾÷¨î?

¦¹³B¥ý°²³]¥þ³¡¾A¥Î, ¥B«á¥O»\«e¥O.
  1. Dim vD

  2. Option Explicit

  3. Private Sub Worksheet_Change(ByVal Target As Range)
  4.   Dim sStr$
  5.   Dim rTar As Range
  6.   
  7. On Error GoTo errWsChg

  8.   With Target
  9.     If .Column = 3 Then ' CÄæ
  10.       If .Offset(, 1) <> "" Then
  11.         sStr = .Text
  12.         vD(CStr(.Value)) = .Offset(, 1)
  13.       Else
  14.         .Offset(, 1) = vD(CStr(.Value))
  15.       End If
  16.     Else ' D Äæ
  17.       If .Offset(, -1) <> "" Then
  18.         sStr = .Offset(, -1).Text
  19.         vD(CStr(.Offset(, -1))) = .Value
  20.       End If
  21.     End If
  22.     For Each rTar In Range([C4], [C25])
  23.       With rTar
  24.         If .Value = sStr And .Offset(, 1) <> "" Then
  25. Application.EnableEvents = False
  26.           .Offset(, 1) = vD(sStr)
  27. Application.EnableEvents = True
  28.         End If
  29.       End With
  30.     Next
  31.   End With
  32.   
  33. On Error GoTo 0
  34. Exit Sub

  35. errWsChg:
  36.   Select Case Err.Number
  37.     Case 13 ' «¬ºA¤£²Å¦X
  38.       Set vD = CreateObject("Scripting.Dictionary")
  39.       Resume
  40.       
  41.     Case Else
  42.       Resume Next
  43.       
  44.     End Select
  45.    
  46.   Resume Next
  47. End Sub

  48. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  49.   With Target
  50.     If Not ((.Row > 3 And .Row < 26) And (.Column > 2 And .Column < 5)) Then
  51.       MsgBox "«D¥i¿é¤Jªº½d³ò(¶È¥i¿ï¾Ü C4 ~ D25), ½Ð­«·sÂI¿ï..."
  52.       If .Row > 3 And .Row < 26 Then
  53.         .Offset(, 3 - .Column).Select ' ¦C¦b¥iÅܧó½d³ò¤º,µJÂI±j¨î²¾¨ì CÄæ
  54.       Else
  55.         .Parent.[C14].Select ' ±j¨î²¾¨ì [C14]
  56.       End If
  57.     End If
  58.   End With
  59. End Sub
½Æ»s¥N½X

TOP

DEAR  luhpro
·PÁ±z¦^ÂФ§¦p¦¹¸Ô²Ó.¤p§Ì«D±`·PÁÂ.¨Ì±z´£¨Ñ¤§µ{¦¡½Xµy­×§ï¤w¥i¥Î
THANKS *10000
ù

TOP

        ÀR«ä¦Û¦b : ¤£­nÀH¤ß©Ò±ý¡A­nÀH¤ß±Ð¨|¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD