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

[µo°Ý] ²Ö¿n³øªí ­×¥¿»yªk

[µo°Ý] ²Ö¿n³øªí ­×¥¿»yªk

½Ð±Ð¦U¦ì¥ý¶i~
¥Ø«e¦³¨â­ÓSHEET
·Q­n§â¨C¤éªº¸ê®Æ½Æ»s¦Ü ¤é²Ö¿n³øªí
[¨C¤é¸ê®Æ]
1.png
2017-1-18 11:37

[¤é²Ö¿n]
2.png
2017-1-18 11:37

¥Ñ©ó¥Ø«e¬O§â¨C¤é¸ê®Æ³v¦æ±a¤J¤é²Ö¿n³øªí¤¤
·Q½Ð°Ý­n¦p¦ó­×¥¿»yªk¤~¯à¦Û°ÊŪ[¨C¤é¸ê®Æ]¦³´X¦æ
½Æ»s¨ì[¤é²Ö¿n]¦³¸ê®Æªº¤U¤@¦æ

¤p©f¥Ø«e»yªk
  1. Sub TEST()
  2. Dim a, b, c, today
  3. Dim i As String

  4.    i = Sheets("¤é²Ö¿n").Range("A65536").End(xlUp).Row  '­pºâ¦³¸ê®Æ³Ì«á¤@¦C¤§¦C¼Æ
  5.    j = Sheets("¤éÀË®Ö").Range("A65536").End(xlUp).Row
  6.    '¤ñ¹ï¤é´Á
  7.       If Worksheets("¤é²Ö¿n").Cells(i, 1).Value = Sheets("¤éÀË®Ö").Range("J2") Then MsgBox "¦­¤w¦s¹L¸ê®Æ¡I"
  8.       
  9.       If Worksheets("¤é²Ö¿n").Cells(i, 1).Value <> Sheets("¤éÀË®Ö").Range("J2") Then
  10.       
  11.         For a = 1 To j - 1
  12.      '¤é´Á¶×¥X
  13.             Sheets("¤é²Ö¿n").Cells(i + a, 1).Value = Sheets("¤éÀË®Ö").Range("J2")
  14.      '³o¸Ì¬O·Q­n­×¥¿ªº¦a¤è ¦]¤éÀˮָê®Æ¦³®É¦h¦³®É¤Ö ¤£¯à¼g¦º
  15.             Sheets("¤é²Ö¿n").Range(Cells(i + 1, 2), Cells(i + 1, 8)) = Sheets("¤éÀË®Ö").Range("A2:G2").Value
  16.             Sheets("¤é²Ö¿n").Range(Cells(i + 2, 2), Cells(i + 2, 8)) = Sheets("¤éÀË®Ö").Range("A3:G3").Value
  17.             Sheets("¤é²Ö¿n").Range(Cells(i + 3, 2), Cells(i + 3, 8)) = Sheets("¤éÀË®Ö").Range("A4:G4").Value
  18.             Sheets("¤é²Ö¿n").Range(Cells(i + 4, 2), Cells(i + 4, 8)) = Sheets("¤éÀË®Ö").Range("A5:G5").Value
  19.             
  20. Next a
  21. MsgBox "¸ê®Æ¶×¥X§¹¦¨¡I"    ' ¶×¥X§¹¦¨°T®§


  22.         End If
  23. End Sub
½Æ»s¥N½X
³Â·Ð¤F~
¬¡­¶Ã¯0118.zip (386.27 KB)

½Ð±Ð¦U¦ì¥ý¶i~
¥Ø«e¦³¨â­ÓSHEET
·Q­n§â¨C¤éªº¸ê®Æ½Æ»s¦Ü ¤é²Ö¿n³øªí
[¨C¤é¸ê®Æ]

[¤é²Ö¿n]

¥Ñ©ó¥Ø«e ...
Changbanana µoªí©ó 2017-1-18 11:42



¸Õ¸Õ¬Ý,¬Ý¬Ý¯à§_À°±o¤W¦£.:)
  1. Sub EX()
  2. Dim Rng, Dr
  3. Dim i As Integer
  4. Dim a

  5. With ThisWorkbook.Worksheets("¤éÀË®Ö")
  6.    Rng = .[a1].CurrentRegion.Offset(1)
  7.    ReDim Dr(UBound(Rng) - 1, 1)
  8.    For i = 1 To UBound(Dr)
  9.         Dr(i, 1) = .[J2]
  10.    Next i
  11.    a = MsgBox("»Ý­n§R°£·í¤éÀË®Ö?", vbInformation + vbYesNo, "¸ß°Ý")
  12.     If a = vbYes Then
  13.         .[a1].CurrentRegion.Offset(1).ClearContents
  14.     End If
  15. End With
  16. With ThisWorkbook.Worksheets("¤é²Ö¿n")
  17.     .[A65535].End(xlUp).Offset(1).Resize(UBound(Dr), 1).Value = Dr
  18.     .[B65535].End(xlUp).Offset(1).Resize(UBound(Rng), UBound(Rng, 2)).Value = Rng
  19. End With

  20. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# justintoolbox

¦³¦¨¥\°õ¦æ³á~~
µ²ªG¤]¬O·Q­nªº
ÁÂÁ§A^^

TOP

¥»©«³Ì«á¥Ñ jsc0518 ©ó 2017-2-7 20:25 ½s¿è

¦^´_ 2# justintoolbox

½Ð±Ð¤@¤U¡A­Y­n±N¤u§@ªí"¤éÀË®Ö"¤ºªº¤é´Á¦Û°Ê¤]§â±a¤J¨ì¤u§@ªí"¤é²Ö¿n" AÄæ¤U
­n¦p¦ó°µ?

001.jpg
2017-2-7 20:22


002.jpg
2017-2-7 20:22


123.rar (352.46 KB)

123.rar (352.46 KB)

FILE

Just do it.

TOP

¦^´_ 4# jsc0518

¸Õ¸Õ~ §Úµyµyµ²¦X¤F¤@¤U
  1. Sub TEST()
  2. Dim Rng, Dr
  3. Dim i, j As String
  4. Dim x As Integer

  5.    i = Sheets("¤é²Ö¿n").Range("A65536").End(xlUp).Row  '­pºâ¦³¸ê®Æ³Ì«á¤@¦C¤§¦C¼Æ
  6.    j = Sheets("¤éÀË®Ö").Range("A65536").End(xlUp).Row
  7.      '¤ñ¹ï¤é´Á
  8.       If Worksheets("¤é²Ö¿n").Cells(i, 1).Value = Sheets("¤éÀË®Ö").Range("J2") Then MsgBox "¦­¤w¦s¹L¸ê®Æ¡I"
  9.       
  10.       If Worksheets("¤é²Ö¿n").Cells(i, 1).Value <> Sheets("¤éÀË®Ö").Range("J2") Then
  11.       
  12.         For a = 1 To j - 1
  13.      '¤é´Á¶×¥X
  14.             Sheets("¤é²Ö¿n").Cells(i + a, 1).Value = Sheets("¤éÀË®Ö").Range("J2")
  15.         Next a
  16.         
  17.     With ThisWorkbook.Worksheets("¤éÀË®Ö")
  18.         Rng = .[a1].CurrentRegion.Offset(1)
  19.         ReDim Dr(UBound(Rng) - 1, 1)
  20.         For x = 1 To UBound(Dr)
  21.             Dr(x, 1) = .[J2]
  22.         Next x
  23.     End With
  24.    
  25.     With ThisWorkbook.Worksheets("¤é²Ö¿n")
  26.         .[A65535].End(xlUp).Offset(1).Resize(UBound(Dr), 1).Value = Dr
  27.         .[B65535].End(xlUp).Offset(1).Resize(UBound(Rng), UBound(Rng, 2)).Value = Rng
  28.     End With
  29.    
  30. MsgBox "¸ê®Æ¶×¥X§¹¦¨¡I"    ' ¶×¥X§¹¦¨°T®§

  31.         End If
  32. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# Changbanana


·PÁ±zªº«ü¾É¡A´ú¸Õ¦¨¥\
Just do it.

TOP

        ÀR«ä¦Û¦b : ÁÀ¨¥¹³¤@¦·²±¶}ªºÂAªá¡A¥~ªí¬üÄR¡A¥Í©Rµu¼È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD