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

[µo°Ý] ­ì¦³¤u§@ªí¤¤¤£¦PÄæ¦ì¸ê®Æ¡AÂಾ¨ì·s²£¥Í¤u§@ªí¤¤¡A¨Ã­«·s¦w±Æ¦ì¸m(¤w¸Ñ¨M)

[µo°Ý] ­ì¦³¤u§@ªí¤¤¤£¦PÄæ¦ì¸ê®Æ¡AÂಾ¨ì·s²£¥Í¤u§@ªí¤¤¡A¨Ã­«·s¦w±Æ¦ì¸m(¤w¸Ñ¨M)

¥»©«³Ì«á¥Ñ jesscc ©ó 2011-4-7 00:07 ½s¿è

¦U¦ì°ª¤â¦n¡A¤p§Ìªì¾Ç¡A¸I¨ì³\¦h²~ÀV¸Ñ¤£¶}¡AÁÙ±æ¦U¦ì¸Ñ´b¡C¬O³o¼Ëªº¡A§Úªº¤u§@¦p¤U­z:
«ö¤U"¸ê®Æ¨Ó·½"¤u§@ªí¤¤ªº"¸ê®ÆÂಾ"«ö¶s«á¡A·|«Ø¥ß¤@­Ó·s¤u§@ªí¡A·s¤u§@ªí¦WºÙ±N¥H"¸ê®Æ¨Ó·½"¤u§@ªí¤¤ªºB3©R¦W¡AµM«á§â"¸ê®Æ¨Ó·½"¤u§@ªí¤¤¬ÛÃö¼Æ¾Ú¶K¨ì³o­Ó·s²£¥Íªº¤u§@ªí¤¤¡A¦p¦P"ªÑ²¼A"¤u§@ªíªº¦w±Æ¨º¼Ë¡C

¦ý¬O²{¦b¥u¼g¨ì«Ø¥ß·sªº¤u§@ªí¡AÀY´ß¤w¸g§ÖÃz¤F¡A¦]¬°¦b«Ø¥ß·s¤u§@ªíªº¦P®É¡AÁÙ­n§PÂ_"¸ê®Æ¨Ó·½"¤u§@ªí¤¤
1.B3¬O§_¬°ªÅ­È(³o³¡¤À¤w¸Ñ¨M)
2.¥HB3¬°¦WºÙªº¤u§@ªí¬O§_¤w¦s¦b¡A­Y¤w¦s¦b¡A´N¤£¥Î«Ø¥ß·sªº¤u§@ªí¡A¥u¶K¤J¸ê®Æ§Y¥i
3.¬Û¦Pªº¤é´Á¡A¬O§_¦b¬Û¦P¤@¦C¤W

¥H¤W¬O¥Ø«e§Ú©Ò¸I¨ìªº°ÝÃD¡A§Æ±æ¦U¦ì°ª¤â¯à±Ð±Ð§Ú¡A·P®¦¤£ºÉ¡C

Test.rar (22.33 KB)

´ú¸ÕÀÉ

«ö¶s.rar (25.88 KB)

´ú¸ÕÀÉ

Jess

¦^´_ 1# jesscc
  1. Sub SourceData_S()
  2. Dim Ay()
  3. With Worksheets("¸ê®Æ¨Ó·½")
  4.     Set Rng = .Range("A3:B3")
  5.     fs = False
  6.     If .Range("B3").Value = "" Then
  7.     MsgBox "µLªk¨ú±oªÑ²¼¦WºÙ,½Ð½T©wªÑ²¼¦WºÙ¤w¶ñ¤JB3Àx¦s®æ", 32, "¸ê®Æ¿ù»~!"
  8.     Exit Sub
  9.     End If
  10.     For Each sh In Sheets
  11.        If sh.Name = .[B3].Text Then fs = True: Exit For
  12.     Next
  13.     If fs = False Then Sheets.Add.Name = .[B3].Text
  14.     ar = Array("A", "C", "I", "P")
  15.     ReDim Preserve Ay(s)
  16.     Ay(s) = Array(.Cells(4, ar(0)).Value, .Cells(4, ar(1)).Value, .Cells(4, ar(2)).Value, .Cells(4, ar(3)).Value)
  17.     s = s + 1
  18.     For i = 5 To .Cells(.Rows.Count, 1).End(xlUp).Row
  19.        If Weekday(.Cells(i, ar(0)), vbMonday) < 5 Then
  20.           ReDim Preserve Ay(s)
  21.           Ay(s) = Array(.Cells(i, ar(0)).Text, .Cells(i, ar(1)).Value, .Cells(i, ar(2)).Value, .Cells(i, ar(3)).Value)
  22.           s = s + 1
  23.           Else
  24.           ReDim Preserve Ay(s)
  25.           Ay(s) = Array(.Cells(i, ar(0)).Text, .Cells(i, ar(1)).Value, .Cells(i, ar(2)).Value, .Cells(i, ar(3)).Value)
  26.           s = s + 1
  27.           ReDim Preserve Ay(s)
  28.           Ay(s) = Array("", "", "", "")
  29.           s = s + 1
  30.         End If
  31.     Next
  32.     With Sheets(Sheets("¸ê®Æ¨Ó·½").[B3].Text)
  33.     Rng.Copy .[A1]
  34.     With .Range(.[A3], .Cells(.Rows.Count, 6))
  35.        .ClearContents
  36.        .Columns(1).NumberFormat = "yyyy/mm/dd"
  37.     End With
  38.     .[A2].Resize(s, 4) = Application.Transpose(Application.Transpose(Ay))
  39.     .Columns("A").AutoFit
  40.     End With
  41.     End With
  42. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·R¦º§A¤F Hsieh ¤j¤j
«ç»ò¨º»ò¼F®`¡A¤~¨S´X¤ÀÄÁªº®É¶¡¡A´N§¹¦¨¤F¡C
§Ú·Q­nªºµ²ªG³£¥X¨Ó¤F¡A¥i¬O¬Ý¤£¤ÓÀ´µ{¦¡ªº¹B§@¡A¹ê¦b¤Ó°ª²`¤F¡C¥i¥H³Â·Ð¤j¤jÁ¿¸Ñ¤@¤U­«ÂI¶Ü?
Jess

TOP

¦^´_ 3# jesscc
  1. Sub SourceData_S()
  2. Dim Ay()
  3. With Worksheets("¸ê®Æ¨Ó·½")
  4.     Set Rng = .Range("A3:B3")
  5.     fs = False
  6.     If .Range("B3").Value = "" Then
  7.     MsgBox "µLªk¨ú±oªÑ²¼¦WºÙ,½Ð½T©wªÑ²¼¦WºÙ¤w¶ñ¤JB3Àx¦s®æ", 32, "¸ê®Æ¿ù»~!"
  8.     Exit Sub
  9.     End If
  10.     For Each sh In Sheets 'Àˬd¤u§@ªí¦WºÙ¬O§_¦s¦b
  11.        If sh.Name = .[B3].Text Then fs = True: Exit For
  12.     Next
  13.     If fs = False Then Sheets.Add.Name = .[B3].Text '¦pªG¤u§@ªí¤£¦s¦b´N·s¼W¤u§@ªí
  14.     ar = Array("A", "C", "I", "P") '»Ý­n´£¨úªºÄæ¦ì
  15.     ReDim Preserve Ay(s) '¡A±N¼ÐÃD¦C¦s¤J°}¦Cªº²Ä¤@µ§¨ÃÂX¤j°}¦C
  16.     Ay(s) = Array(.Cells(4, ar(0)).Value, .Cells(4, ar(1)).Value, .Cells(4, ar(2)).Value, .Cells(4, ar(3)).Value)
  17.     s = s + 1
  18.     For i = 5 To .Cells(.Rows.Count, 1).End(xlUp).Row '¶i¤J¸ê®Æ°j°é
  19.        If Weekday(.Cells(i, ar(0)), vbMonday) < 5 Then '§PÂ_¤é´Á¬°¬P´Á´X¡A¬P´Á5¥H«e°õ¦æ
  20.           ReDim Preserve Ay(s) '±N¸ê®Æ¦s¤J°}¦C
  21.           Ay(s) = Array(.Cells(i, ar(0)).Text, .Cells(i, ar(1)).Value, .Cells(i, ar(2)).Value, .Cells(i, ar(3)).Value)
  22.           s = s + 1
  23.           Else '¬P´Á¤­°õ¦æ
  24.           ReDim Preserve Ay(s) '±N¸ê®Æ¦s¤J°}¦C
  25.           Ay(s) = Array(.Cells(i, ar(0)).Text, .Cells(i, ar(1)).Value, .Cells(i, ar(2)).Value, .Cells(i, ar(3)).Value)
  26.           s = s + 1
  27.           ReDim Preserve Ay(s) 'Àx¦s¤@­ÓªÅ¥Õ¦C¨ì°}¦C
  28.           Ay(s) = Array("", "", "", "")
  29.           s = s + 1
  30.         End If
  31.     Next
  32.     With Sheets(Sheets("¸ê®Æ¨Ó·½").[B3].Text)
  33.     Rng.Copy .[A1] 'ªÑ²¼¦WºÙ
  34.     With .Range(.[A3], .Cells(.Rows.Count, 6))
  35.        .ClearContents '²M°£­ì¨Ó¸ê®Æ
  36.        .Columns(1).NumberFormat = "yyyy/mm/dd" '³]©wAÄ欰¤é´Á®æ¦¡
  37.     End With
  38.     .[A2].Resize(s, 4) = Application.Transpose(Application.Transpose(Ay)) '±N°}¦C­È¼g¤J¤u§@ªí
  39.     .Columns("A").AutoFit 'AÄæ¦Û°ÊÄæ¼e
  40.     End With
  41.     End With
  42. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¹ê¦b¨ØªA¨ì¤­Åé§ë¦a¡A±j!
Jess

TOP

¤j¤j¤S¨Ó³Â·Ð±z¤F¡A¯u¤£¦n·N«ä¡C¥»¨Ó§Ú¬O·Q¦Û¤v­×§ïªº¡A¥i¬O¬ã¨s¤F¤@¤U¤È¡A¯à¤O¹ê¦b¤£°÷¡A§ï¤£¥X¨Ó¡C

¬O³o¼Ëªº¡A§Ú·Q¦b·s¼Wªº¤u§@ªí¤¤¦A¦P®É¥[¤JC1¤ÎE2¤å¦r©MD1(§Q¥ÎDDE¨ú­È=YES|DQ!'2882.Capital')¡A2882¬OªÑ²¼¥N¸¹¡A§Q¥Î"¸ê®Æ¨Ó·½"¤u§@ªí¤¤ªºA3¨ú±o¡C
µM«á¦bEÄæ¡A±qE3¥H¤U¶}©l­pºâC3*D3/D1,C4*D4/D1,C5*D5/D1,.....¡A¤£ª¾¸Ó«ç»ò°µ?

ÁÙ¦³¤@ÂI¤]¬O­è­èµo²{ªº¡A°²³]²{¦b"°ê®õª÷"¦³·s¸ê®Æ(2011/1/17¶}©l)¡A­n¥[¤J¨ì¸ê®Æ2011/1/14¤§«á¡A­n¦p¦ó°µ¤~·|¶K¨ì¸ê®Æ¤§«á¡A¦Ó¤£·|Âл\±¼Â¸ê®Æ?

¤wªþ¤W·s´ú¸ÕÀÉ
Jess

TOP

¦^´_ 6# jesscc
  1. Sub SourceData_S()
  2. Dim Ay()
  3. With Worksheets("¸ê®Æ¨Ó·½")
  4.     Set Rng = .Range("A3:B3")
  5.     fs = False
  6.     If .Range("B3").Value = "" Then
  7.     MsgBox "µLªk¨ú±oªÑ²¼¦WºÙ,½Ð½T©wªÑ²¼¦WºÙ¤w¶ñ¤JB3Àx¦s®æ", 32, "¸ê®Æ¿ù»~!"
  8.     Exit Sub
  9.     End If
  10.     For Each sh In Sheets 'Àˬd¤u§@ªí¦WºÙ¬O§_¦s¦b
  11.        If sh.Name = .[B3].Text Then fs = True: Exit For
  12.     Next
  13.     If fs = False Then Sheets.Add.Name = .[B3].Text '¦pªG¤u§@ªí¤£¦s¦b´N·s¼W¤u§@ªí
  14.     ar = Array("A", "C", "I", "P") '»Ý­n´£¨úªºÄæ¦ì
  15.     If fs = False Then '¦pªG¬O·s¼W¤u§@ªí¡A´N¦s¤J¼ÐÃD
  16.     ReDim Preserve Ay(s) '±N¼ÐÃD¦C¦s¤J°}¦Cªº²Ä¤@µ§¨ÃÂX¤j°}¦C
  17.     Ay(s) = Array(.Cells(4, ar(0)).Value, .Cells(4, ar(1)).Value, .Cells(4, ar(2)).Value, .Cells(4, ar(3)).Value, "¦¨¥æ¶q¥eªÑ¥»¤ñ¨Ò")
  18.     s = s + 1
  19.     End If
  20.     For i = 5 To .Cells(.Rows.Count, 1).End(xlUp).Row '¶i¤J¸ê®Æ°j°é
  21.        If Weekday(.Cells(i, ar(0)), vbMonday) < 5 Then '§PÂ_¤é´Á¬°¬P´Á´X¡A¬P´Á5¥H«e°õ¦æ
  22.           ReDim Preserve Ay(s) '±N¸ê®Æ¦s¤J°}¦C
  23.           Ay(s) = Array(.Cells(i, ar(0)).Text, .Cells(i, ar(1)).Value, .Cells(i, ar(2)).Value, .Cells(i, ar(3)).Value, "=RC[-2]*RC[-1]/R1C4")
  24.           s = s + 1
  25.           Else '¬P´Á¤­°õ¦æ
  26.           ReDim Preserve Ay(s) '±N¸ê®Æ¦s¤J°}¦C
  27.           Ay(s) = Array(.Cells(i, ar(0)).Text, .Cells(i, ar(1)).Value, .Cells(i, ar(2)).Value, .Cells(i, ar(3)).Value, "=RC[-2]*RC[-1]/R1C4")
  28.           s = s + 1
  29.           ReDim Preserve Ay(s) 'Àx¦s¤@­ÓªÅ¥Õ¦C¨ì°}¦C
  30.           Ay(s) = Array("", "", "", "", "")
  31.           s = s + 1
  32.         End If
  33.     Next
  34.     With Sheets(Sheets("¸ê®Æ¨Ó·½").[B3].Text)
  35.     Rng.Copy .[a1] 'ªÑ²¼¦WºÙ
  36.     .[C1] = "ªÑ¥»(±i)": .[D1].FormulaLocal = "=YES|DQ!'" & .[a1] & ".Capital'*1000"
  37.     With .Range(.[A3], .Cells(.Rows.Count, 6))
  38.        '.ClearContents '²M°£­ì¨Ó¸ê®Æ
  39.        .Columns(1).NumberFormat = "yyyy/mm/dd" '³]©wAÄ欰¤é´Á®æ¦¡
  40.     End With
  41.     .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(s, 5) = Application.Transpose(Application.Transpose(Ay)) '±N°}¦C­È¼g¤J¤u§@ªí
  42.     .Columns("A:E").AutoFit 'A:EÄæ¦Û°ÊÄæ¼e
  43.     End With
  44.     End With
  45. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦¬¨ì¤F¡C
±z¯u¬O¤j·R¤§¤H¡A¤µ«á­n»{¯u¦V±z¾Ç²ß¡C
ÁÂÁÂ!
Jess

TOP

Hsieh ¤j
§Ú¬ã¨s¤F¨â¤ÑÁÙ¬O¦³«Ü¦h°ÝÃD
4¡B5¡B11¦æªº Rng¡Bfs¡Bsh ¬O«O¯d¦r¶Ü?
Àx¦s®æªº¿ï¨ú¦³¦n¦hºØ¤è¦¡¡A¨ì©³¦³¤°»ò®t§O?¦p6¡B35¦æ
¥t¥~²Ä41¦æ¡A¾ã¦æ³£¬Ý¤£À´@@
Jess

TOP

¦^´_ 9# jesscc

    Rng,sh,fs³o¨Ç¤£¥s«O¯d¦r¡A³o¨ÇºÙ¬°ÅܼÆ
¥Ñ¦Û¤vÀ°¬Y­ÓÀH®ÉÅܰʪº­È¡A©Ò¨ú¦W¦r¡A´N¹³°ê¤¤¼Æ¾Çªº¥N¼Æ¬O¦P¼Ëªº·N¸q
¦Ü©óÀx¦s®æªº¼gªk¦³«Ü¦h
¼Ð·Ç¼gªkCells(row,column)
¦b¬A¸¹¤º¿é¤J¦C¸¹»PÄ渹
³o¬O«ü©w³æ¤@Àx¦s®æªº¼Ð·Ç¼gªk
­n«ü©w½d³ò®ÉRange(address)
¦b¬A¸¹¤º¿é¤J½d³òªº¦ì§}¦r¦ê
³o¬O«ü©w½d³òªº¼Ð·Ç¼gªk
¥t¤@ºØ¥H¤¤¬A¸¹ªí¥Üªº¤èªk[name]
¦¹ªk¬O¤@ºØª«¥ó¥]¸Ë¼gªk¡A¬A¸¹¤º¿é¤Jªº¬O¥Nªí½d³òªº¦WºÙ
¦p[A1]¡AA1¦b¤u§@ªí¤¤©Ò¾Ö¦³ªº·N¸q¬O«ü¡A²Ä¤@¦C²Ä¤@ÄæÀx¦s®æªº¦W¦r
¦Ü©ó²Ä41¦æ.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(s,5)
³o¬O¼Ð·ÇªºCELLS¼gªk¡A§A¥²¶·©î¶}¨Ó¸ÑÄÀ´N¯à¤F¸Ñ
.Cells(.Rows.Count, 1)¬A¸¹¤¤²Ä¤@­Ó¤Þ¼Æ¬O¦C¸¹¡A³oùبϥÎ.Rows.Count
¬O¦]¬°²{¦bEXCELªºª©¥»¤£¦P¡A¤u§@ªíªºÁ`¦C¼Æ·|¤£¦P
§A¬O2003ª©¥»©Ò¥H³o¸Ì§ï¦¨65536¤]¬O¤@¼Ëªº
³o¬O­n±o¨ìAÄæ³Ì©³¤U¤@¦CªºÀx¦s®æ
End(xlUp)¬O¦V¤W¨ì¸ê®Æªº³Ì©³³¡
Offset(1, 0)¬O¦V¤U¤@®æªº¦ì¸m
Resize(s,5)¬O°ò·ÇÀx¦s®æ¦ì¸m¦V¤Us¦C¦V¥k5ÄæÂX®iªº½d³ò
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD