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

[µo°Ý] ½Ð°Ý¡u³Ì«á¤@¦æ¸ê®Æ ¤U©Ô½Æ»s¡v¨ì·sªº¤@¦æ¡A¦p¦ó¼g¦¨¥¨¶°©ÎVB©O¡H

[µo°Ý] ½Ð°Ý¡u³Ì«á¤@¦æ¸ê®Æ ¤U©Ô½Æ»s¡v¨ì·sªº¤@¦æ¡A¦p¦ó¼g¦¨¥¨¶°©ÎVB©O¡H

½Ð°Ý¦U¦ì¤j¤j¡G
¦]¬°¨C¤é³£­n§ó·s¤@µ§¸ê®Æ¡A©Ò¥H³£¬O¡u¿ï¨ú¡v¡u³Ì«á¤@¦æ¡v¡A¦A©ì¦²¦V¤U©Ô(½Æ»s)¨ì·sªº¤@¦æ¡C
¦p¹Ï¡G½Æ»s5¤ë2¤éªº¡uA7:E7¡v¡AµM«á¦V¤U©Ô¡AÅܦ¨¦h¤@¦æ 5¤ë3¤éªº¡uA8:E8¡v¡C
            ¥B¨C¤é¦V¤U·s¼W¤@¦æ5¤ë4¤é¡uA9:E9¡v¡B5¤ë5¤é¡uA10:E10¡v¡B5¤ë6¤é¡uA11:E11¡v........

½Ð¦U¦ì¤j¤jÀ°¦£¡A³o¼Ëªº°Ê§@¡A­n¦p¦ó¼g¦¨¥¨¶°©O¡HÁÙ¬O»Ý­n¥ÎVB¼g©O¡H
¤U©Ô½Æ»s.png

¦^´_ 1# cslu37
¸Õ¸Õ¬Ý
³o¤u§@ªí Sheet¼Ò²Õ ªºµ{¦¡½X
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng As Range
  4.     Set Rng = Range("A2:E2")
  5.     If Rng.End(xlDown).Row <> Rows.Count Then
  6.         '¤u§@ªíªºÁ`¦C¼Æ: Rows.Count 2003=65536,2010=1048576
  7.         Set Rng = Range(Rng, Rng.End(xlDown))
  8.         '"A2:E2" ©¹¤U©µ¦ù¨ì³Ì«á¤S¸ê®Æªº¦ì¸m
  9.     End If
  10.     'Range.AutoFill ¤èªk ¥i°Ñ»¡©ú
  11.     With Rng
  12.         .AutoFill Destination:=.Rows("1:" & .Rows.Count + 1), Type:=xlFillDefault
  13.         '.Rows("1:" & .Rows.Count + 1)  Rngªº¦C¼Æ+1ªº½d³ò
  14.     End With
  15.    
  16. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE


    ÁÂÁÂGBKEE¤j¤j³á:loveliness: ¡I¡I

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2019/5/9 07:43): ½ÐªþÀɬݬÝ

¦^´_ 2# GBKEE


½Ð°Ý GBKEE ¤j¤j¡G
¥Ø«e¹J¨ìªº°ÝÃD...
´N¬OExcel¦@¦³¤T­ÓSheet(Sheet1¡BSheet2¡BSheet3)¡A¦ý¤T­ÓSheet²{¦³ªº¦æ¼Æ³£¤£¦P¡A»Ý­n¤U©ÔªºÄæ¦ì¤]¤£¦P¡A
·Q­n°µ¤@­Ó«ö¶s¡A«ö¤@¤U«ö¶s¡A´N¥i¥H¤@¦¸©ì¦²¤U©Ô¤T­ÓSheet³Ì«á¤@¦æªº¸ê®Æ©O?
Àµ½ÐGBKEE¤j¤j«ü¾É¡I¡IÁÂÁ±z¡I¡I

TOP

¦^´_ 2# GBKEE

·PÁÂGBKEE¤j¤j¡Aªþ¤WÀɮסA³Â·ÐGBKEE¤j¤j«ü¾É¡IÁÂÁ±z¡I¡I

¤u§@ªí1¡÷AÄæ~EÄæ¡A±q14¦æ ©ì¦²¤U©Ô¨ì15¦æ¡C
¤u§@ªí2¡÷AÄæ~GÄæ¡A±q27¦æ ©ì¦²¤U©Ô¨ì28¦æ¡C
¤u§@ªí3¡÷AÄæ~IÄæ¡A±q41¦æ ©ì¦²¤U©Ô¨ì42¦æ¡C

©ì¦²¤U©Ô20190409.rar (14.41 KB)

TOP

¦^´_ 5# cslu37

¨Ì§AªþÀɤº®e©Ò¼gµ{¦¡½X
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Dim Rng As Range
  3. Sub Ex_¤U©Ô1()
  4.     Dim i As Integer, AR(), E As Variant
  5.     AR = Array(¤u§@ªí2, ¤u§@ªí3)
  6.     '***¤lµ{¦¡ªº¤è§b±¹¬I*****
  7.     For Each E In AR  '°j°é e : ¨Ì§Ç¶Ç¦^ °}¦C¤ºªº¤u§@ªíª«¥ó
  8.         If TypeName(E) <> "Worksheet" Then MsgBox "°}¦C¤¸¯À»Ý¬O   Worksheet ª«¥ó": End
  9.     Next
  10.     '*************************
  11.     With Sheets("¤u§@ªí1")
  12.         Set Rng = .Range("a3", "e" & .Range("a2").End(xlDown).Row)
  13.         If .Range("a2").End(xlDown).Row = Rows.Count Then
  14.             Set Rng = .Range("A3")   '¤u§@ªí1©|¥¼¦³¸ê®Æ
  15.             Rng = Date
  16.             For i = 2 To 5  '°j°é:¨Ì§Ç¿é¤J¸ê®Æ
  17.                 '**¤£ª¾¤½¦¡¬°©M**
  18.                 Rng.Cells(1, i) = i
  19.             Next
  20.         Else
  21.             '¤u§@ªí1¤w¦³¸ê®Æ , ¥Î½d³ò¤U©Ô¤èªk
  22.             If Rng.Rows.Count = 1 Then ''**¦]¤U©Ôªº½d³ò¦C¼Æ¥u¦³¤@¦C***
  23.                 .Select
  24.                 Rng.Cells(2, 1).Activate
  25.                 MsgBox "¤U©Ôªº½d³ò¦C¼Æ¥u¦³¤@¦C" & vbLf & "½Ð¦Û¦æ¿é¤J" & Rng.Offset(1).Address & "ªº¼Æ­È...."
  26.                 End
  27.                
  28.             End If
  29.             Rng.AutoFill Destination:=Rng.Rows("1:" & Rng.Rows.Count + 1), Type:=xlFillDefault
  30.             Set Rng = Rng.Cells(Rng.Rows.Count + 1, 1) 'AÄæ½d³ò¤º³Ì«áªº¤é´Á
  31.         End If
  32.     End With
  33.     Ex_¤U©Ô2 AR  '©I¥s ¤lµ{§Ç
  34. End Sub
  35. Sub Ex_¤U©Ô2(Sh As Variant)  '¤lµ{§Ç »Ý¶Ç»¼°Ñ¼Æ(°}¦C(¤u§@ªíª«¥ó))
  36.     Dim xRng As Range, AR(), E As Variant
  37.     For Each E In Sh  '°j°é e : ¨Ì§Ç¶Ç¦^ °}¦C¤ºªº¤u§@ªíª«¥ó
  38.         If E.Name = "¤u§@ªí2" Then
  39.             '¸ê®Æ2-3 ¸ê®Æ2-4 ¸ê®Æ2-5 ¸ê®Æ2-6 ªº¤½¦¡
  40.             AR = Array("=rc[-2]*1+rC[-1]*1", "=rC[-3]*rC[-2]+rC[-1]", "=rC[-3]*rC[-2]-rc[-1]", "=rc[-3]+rc[-2]-rc[-1]")
  41.         ElseIf E.Name = "¤u§@ªí3" Then
  42.             '¸ê®Æ3-3 ¸ê®Æ3-4 ¸ê®Æ3-5 ¸ê®Æ3-6 ¸ê®Æ3-7 ¸ê®Æ3-8  'ªº¤½¦¡
  43.             AR = Array("=rc[-2]+rc[-1]", "=rc[-1]-rc[-2]", "=rc[-2]+rc[-1]", "=rc[-2]*rc[-1]", "=rc[-2]+rc[-1]", "=rc[-2]-rc[-1]")
  44.         Else
  45.             MsgBox "¤u§@ªí¿ù»~ µ{¦¡Ãö³¬": End
  46.         End If
  47.         Set xRng = E.Range("A:A").Find(Rng.Text, LookIn:=xlValues) '´M§ä¤u§@ªí1AÄæ½d³ò¤º³Ì«áªº¤é´Á
  48.         If xRng Is Nothing Then   '¨S§ä¨ì
  49.             Set xRng = E.Range("A" & Rows.Count).End(xlUp).Offset(1)
  50.             '³]¬°AÄæ½d³ò¤º³Ì«áªº¤é´Áªº¤U¤@¦C¦ì
  51.         Else
  52.             If xRng.Range("A2") <> "" Then  '§ä¨ì ¥B ¤U¤@¦C¦ì<>""
  53.                 With xRng
  54.                     Range(.Cells(2, 1), .Range("A2").End(xlToRight).End(xlDown)) = ""
  55.                     '²M°£¤U¤@¦C¦ì«áªº¸ê®Æ
  56.                 End With
  57.             End If
  58.         End If
  59.         xRng.Resize(, 3).Value = Rng.Resize(, 3).Value  'µ¹¤W¤u§@ªí¤U©ÔA:Cªº¸ê®Æ
  60.         With xRng.Cells(1, 4).Resize(, UBound(AR) + 1)
  61.             .Cells = AR 'µ¹¤W¤u§@ªí2,¤u§@ªí3:C«á¸ê®Æªº¤½¦¡
  62.             '***** ¦p¤W¦C µ¹¤W¤u§@ªí2,¤u§@ªí3:C«á¸ê®Æªº¤½¦¡ ¥¿½T
  63.            .Cells = .Value '***** ¥i¥Î¤U¦C¦¹µ{¦¡½X±N³B¦s®æªº¤½¦¡Âର­È
  64.          '*************************************************
  65.         End With
  66.     Next
  67. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¥»©«³Ì«á¥Ñ cslu37 ©ó 2019-5-11 00:15 ½s¿è
¦^´_  cslu37
¸Õ¸Õ¬Ý
³o¤u§@ªí Sheet¼Ò²Õ ªºµ{¦¡½X
GBKEE µoªí©ó 2019-5-5 16:15
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng As Range
  4.     Set Rng = Range("A2:E2")
  5.     If Rng.End(xlDown).Row <> Rows.Count Then
  6.         '¤u§@ªíªºÁ`¦C¼Æ: Rows.Count 2003=65536,2010=1048576
  7.         Set Rng = Range(Rng, Rng.End(xlDown))
  8.         '"A2:E2" ©¹¤U©µ¦ù¨ì³Ì«á¤S¸ê®Æªº¦ì¸m
  9.     End If
  10.     'Range.AutoFill ¤èªk ¥i°Ñ»¡©ú
  11.     With Rng
  12.         .AutoFill Destination:=.Rows("1:" & .Rows.Count + 1), Type:=xlFillDefault
  13.         '.Rows("1:" & .Rows.Count + 1)  Rngªº¦C¼Æ+1ªº½d³ò
  14.     End With
  15.    
  16. End Sub
½Æ»s¥N½X
GBKEE¤j¤j±z¦n¡G
    §Ú¹Á¸ÕµÛ§ï¦¨©Ô¨ì¤U¤@¦æ«á¡A¦A±N¾ã¦æ§ï¦¨"­È"¡A¦ýª¦¤åª¦¤F¤@¾ã±ß¡A¤]¹Á¸Õ­×§ï...¦ý¤@ª½¥X²{¿ù»~°T®§>"<
    ½Ð¤j¤j«ü¾É¡A­n¦b­þ¥[¤Wvalue¡A¤~¯à¦b¤U©Ô«á  ¦A§ï¦¨"­È"©O¡H
    ·PÁÂGBKEE¤j¤j¡I¡I

TOP

¦^´_ 7# hcm19522


  ÁÂÁ hcm19522 ªº¤À¨É¡I¡I
  ¥u¬O¹J¨ì¤@­Ó°ÝÃD...´N¬O...¤é´Á·|¦Û°Ê±NªÅªº¤é´Á¦Û°Ê¸É¤W ¨Ã³sÄò...
  ¨Ò¦p¡G¦]¬°¤»¤é¤£»Ý­n§ó·s¡A©Ò¥H5/10 ±µµÛ´N¬O 5/13¡A¦ýG2Åã¥Ü5/13«á¡A·|¦Û°Ê±N5/11¡B5/12¤]¶ñ¤J...
              Åܦ¨5/10
                      5/11
                      5/12
                      5/13
    ½Ð°Ý¦p¦ó¤~¯à±N¶g¥½¦©°£©O¡AÀµ½Ðhcm19522¤j¤j«ü¾É¡AÁÂÁ±z¡I¡I

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : §g¤l¥ß«í§Ó¡A¤p¤H«í¥ß§Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD