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

[µo°Ý] ¦p¦ó±NDATA¤¤¦P¸ê®Æ¡A©ñ¨ì¤£¦P½s¸¹ªºªí®æ¤º?

[µo°Ý] ¦p¦ó±NDATA¤¤¦P¸ê®Æ¡A©ñ¨ì¤£¦P½s¸¹ªºªí®æ¤º?

¦U¦ì¤j¤j:
A¤À­¶ªº¤º®e¡A¬O¥ÑDATA¤À­¶¥Î¨ç¼Æ±a¨ìA¤À­¶(Âê¦í­¶­±)¡A


¤§«eªº¤è¦¡¬O¦bDATA­¶­± ¥ÎABC027 / ABC025 ¸òNO ¨Ó°Ï¤Àªí®æ¤º®e

²{¦b°ÝÃD¬O ¦p¦ó¦bDATA­¶­±¥[¤J¤°»ò°Ñ¼Æ«á¡AA­¶­±ªºABC027 / ABC025 ªí®æ¤º®e·|¤@¼Ë(©Î¬O§ó¦hABC024)?



½d¥».zip (132.98 KB)

¥»©«³Ì«á¥Ñ luhpro ©ó 2018-1-10 23:41 ½s¿è

¤U¤èªþÀÉ : ¥u­nÅܧó¤u§@ªí A ¤¤¥ô¤@ ¡° ¤U¤èÀx¦s®æ(¦@¥|­Ó)¤º®e, ¨ä¤Uªí®æ¤º®e´N·|§Y®É§ó·s.
===== ¥H¤U¤º®e©ñ¦b Module =====
  1. Public vData
½Æ»s¥N½X
===== ¥H¤U¤º®e©ñ¦b ThisWorkBook =====
  1. Private Sub Workbook_Open()
  2.   Dim iCol%
  3.   Dim lRow&
  4.   
  5.   Set vData = CreateObject("Scripting.Dictionary")
  6.   lRow = 2
  7.   With Sheets("DATA")
  8.     While .Cells(lRow, 4) & .Cells(lRow, 9) <> ""
  9.       If .Cells(lRow, 2) <> "" Then
  10.         vData(.Cells(lRow, 2) & "_" & .Cells(lRow, 3)) = lRow
  11.       End If
  12.       lRow = lRow + 1
  13.     Wend
  14.   End With
  15. End Sub
½Æ»s¥N½X
===== ¥H¤U¤º®e©ñ¦b ¤u§@ªí5 (A) =====
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   Dim iI%
  3.   Dim lRow&
  4.   Dim rSou As Range, rTar As Range
  5.   Dim wsSou As Worksheet
  6.   
  7.   Set wsSou = Sheets("DATA")
  8.   With Target
  9.     Select Case "R" & .Row & "C" & .Column
  10.       Case "R4C2", "R29C2", "R4C18", "R29C18"
  11. Application.EnableEvents = False
  12.         .Offset(2).Resize(20, 6).ClearContents
  13. Application.EnableEvents = True
  14.         For iI = 1 To 20
  15.           If vData.Exists(.Value & "_" & iI) Then
  16.             lRow = vData(.Value & "_" & iI)
  17. Application.EnableEvents = False
  18.             wsSou.Cells(lRow, 4).Resize(, 5).Copy .Offset(1 + iI)
  19. Application.EnableEvents = True
  20.           Else
  21.             Exit For
  22.           End If
  23.         Next
  24.         With .Offset(2).Resize(20, 6)
  25.           .Font.Size = 16
  26.           With .Borders(xlInsideVertical) ' ¦r¤Ó¤p,®Ø½u¤£¨£½Õ¾ã
  27.             .LineStyle = xlContinuous
  28.             .ColorIndex = 0
  29.             .TintAndShade = 0
  30.             .Weight = xlThin
  31.           End With
  32.           With .Borders(xlInsideHorizontal)
  33.             .LineStyle = xlContinuous
  34.             .ColorIndex = 0
  35.             .TintAndShade = 0
  36.             .Weight = xlThin
  37.           End With
  38.         End With
  39.     End Select
  40.   End With
  41. End Sub
½Æ»s¥N½X
½d¥»_Ans.zip (141.89 KB)

TOP

¦^´_ 2# luhpro


   ·PÁ¤j¤jªºÀ°¸Ñµª¡AÁٻݭn¦n¦nªº®ø¤Æ¤@¤U¡C

TOP

        ÀR«ä¦Û¦b : µoµÊ®ð¬Oµu¼ÈªºµoºÆ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD