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

[µo°Ý] ¹ïÀ³¤é´Á¶K½LÂI¸ê®Æ

¦^´_ 10# °a¤ªºµ

¦Aªþ¤W2­Ó´ú¸ÕÀÉ ¶K½LÂI¸ê®Æ_´ú¸Õ2.rar (330.44 KB)
µ{¦¡§Ú¦h´ú¸Õ¨Ç¦A¦^ÂÐ
½Ð°Ýµ{¦¡³Æµù,¯à§_¦A¸Ô²Ó¨Ç?

TOP

¦^´_ 10# °a¤ªºµ
ºµ¤j,
½ÐÀ°¦£¬ã¨s¤@¤U,
a) ¥ØªºÀɦ³¤GºØ©T©w®æ¦¡,¥Bkey-inªºÄæ¦ì½d³ò¬Û¦P,¥u¦³¦X¨ÖÀx¦s®æ¤À2¦C&3¦C
b) ¦Ó¨Ó·½ÀÉÁöµM½LÂI½d³ò¤£¦P,¦ý®æ¦¡¤j­P¬Û¦P,¥u¦³¦C¼Æªº¤£¦P,¤]¤À¬°¤GºØ®æ¦¡
1) ½LÂI°Ï¦b(½L®t)(¹w³]¤Ñ¼Æ)¤§¶¡
2) ½LÂI°Ï¦b(µ²¾l¦X­p)(½L®t)¤§¶¡
¦³¨S¦³¥i¯àµ{¦¡¥i¥H¥u§ïÀɦW,´N¥i¥H¾A¥Î©ó±q¨Ó·½¶K¨ì¥ØªºÀÉ?
(³Ì¦h´N¤À¦¨2­Óµ{¦¡,¤À§O¬O¶K¨ì½LÂI¡Bstock)
¦]¬°«È¤á·|¦³¼W¥[ªº¥i¯à©Ê,«~¦W¤]¬O,
§Æ±æ¯à°÷¦b¼W¥[«È¤á&«~¦W®É,¥u­n¦b¤u§@ªí©¹¤U¼W¥[,´N¥i¥H¨Ï¥Î,
¤£­n¦A¥h­×§ïµ{¦¡¡I
¥t¥~ÁÙ·Q½Ð°Ý,ÀɦW¦]¬°³£«Üªø,§Ú·Q§ï¬°¶}ÀɮɥÎex: *¥Ã»·*,³o¼Ëªº¤èªk,
§Ú¥uª¾¹D³oºØ¼gªk,¤£ª¾ÁÙ¦³¨ä¥L¤è¦¡¶Ü?
   Do While File <> ""
        With Workbooks.Open(Path & File)
------
   File = Dir
Loop
ÂAªá®w¦sªí.rar (148.25 KB)

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2021-7-16 05:02 ½s¿è

¦^´_ 11# PJChen
stock ¤u§@ªí §Ú¨S¦³§ï ¦]¬°§Ú³oª½¦³¤@ºØ®æ¦¡ ¦pªGÁÙ¦³ ½Ð¦A¦hµ¹´X­Ó  ¥u­n§â­n¶KªºÀɮ׳£©ñ¦b¦P¤@­Ó¸ê®Æ§X´N¥i¥H¤F¤£¥Î§ïÀɦW
¦³ªÅ¦AÀ°§Ú¸Õ¸Õ¬Ý ³o¼Ë¥i¤£¥i¥H µù¸Ñ¦³¥[¤F¤@¨Ç  ¦pªG¤£°÷²M·¡ ¥i¥H¦A¸ò§Ú»¡¬O­þ¤@¬q ·PÁÂ
¦]¬°³o¼gªk¤£¬O«Ü¦n ¬Ý°_¨Ó«Ü¶Ã ¦Ó¥B°j°é¤]¤Ó¦h¤F ¦pªGÀɮצhªº¸Ü·|«ÜºC ¬Ý¬Ý¦³¨S¦³¤j¤jÄ@·NÀ°¦£  §Ú¤]¥i¥H¶¶«K¾Ç²ß ^^"
·Pı§ÚªºÅ޿観°ÝÃD >"<
¶K½LÂI¸ê®Æ0716.rar (836.26 KB)

TOP

¦^´_ 13# °a¤ªºµ
ºµ¤j¦n,
1) stock¤u§@ªí,¤£¯à§ï¬°Sheets(¼Æ¦r),¹ê»Ú¨Ï¥ÎÀɧt¦³¨ä¥L¤u§@ªí,³o¼Ë·|¥X¿ù
2) stockªº¨Ó·½ÀÉ,¥u­n«~¦W¶¶§Ç¤£¦P,´NµLªk¶K¤W¸ê®Æ,µ{¦¡´N¤¤Â_¤F
3) Workbooks("¦h«È¤á½LÂIªí.xlsx").Sheets("½LÂI")¤]¬O¬Û¦P±¡§Î,¤£¯à§ï¬°Sheets(¼Æ¦r)
4) ¤½¥qªº¦s©ñ°Ï,¦³«Ü¦hÀÉ,¤£¥u¬O³o´X­ÓÀÉ®×,¥B¦³Ãþ¦üÀɦW,©Ò¥H¥ÎDir(U & "*.xlsx")¤]·|¥X¿ù
     ¤W¦¸¸ß°Ý:ÀɦW¦]¬°³£«Üªø,·Q§ï¬°¶}ÀɮɥÎex: *¥Ã»·*,³o¼Ëªº¤èªk,¤£ª¾¥i§_?
5) ¤G­Óµ{¦¡³£»Ý­n¦³¤ñ¹ï«~¦Wªº¥\¯à¤~¦æ,¤£µM¤@¥¹«~¦W±Æ¦C¤£¦P,´N·|¥X¿ù¤F
Re¶K½LÂI¸ê®Æ0716.rar (944.92 KB)

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2021-7-20 02:05 ½s¿è

¦^´_ 14# PJChen
½Ð°Ý ¦³¤é´Á³¡¤ÀªºÀx¦s®æ ¦ì¸m¬O¤£¬O©T©wªº? ÁÙ¬O¨C¤@­Ó¬¡­¶Ã¯³£¤£¤@¼Ë? ¦C¦p ¸U¹F®w¦sªí ¦³¤é´Áªº³¡¤ÀÄæ¦ì®æ¦¡
¦]¬°§Ú¬Ý¨ä¥¦ªº¬¡­¶Ã¯ ®æ¦¡³£¬O¤£©T©wªº ¦³ªº¬O±qv7 ¶}©l ¦³ªº¤£¬O  ¬O¦]¬°³o¬O´ú¸ÕÀɮתºÃö«Y ÁÙ¬O­ì©lÀɮפ]¬O³o¼Ë?

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2021-7-20 10:32 ½s¿è

¦^´_ 13# °a¤ªºµ
¦^´_ 14# PJChen

¦³®É­Ô¤£¬O§Ú¤£·QÀ°¡A¬O§Ú³s»Ý¨D³£¨S¬ÝÀ´......

·Pı¼Ó¥Dªº°ÝÃD³£¬O¹ê°È¤Wªº°ÝÃD¡A¹ï¤£¼ô§A­Ì¤½¥qªí®æ¶ñ¼g¡B¹B§@¤è¦¡ªº¤H¨Ó»¡¡A¦³ÂI½ÆÂø

¸Û¤ß«Øij¡A¼Ó¥D¥i¥H¸ÕµÛ§â½ÆÂøªº°ÝÃD¡A²³æ¤Æ¤§«á¦Aµo°Ý¡A¤@©w·|¤ñ¸û¦h¤HÀ°¦£

¦]¬°¬Ý§A¤§«eªºµo°Ý¡A±À´ú¥X§A¦³¤@¨Ç¨ÇªºVBA°ò¦ªº (§Ú°O±o§A¦³°Ý¹LUbound¤§Ãþªº)

µ{¦¡¥i¥H¹³«÷¹Ï¤@¼Ë¡A¥i©î¤À¦¨«Ü¦h¤p³¡¤Àªº

°²¦p§Aªº»Ý¨D»Ý­nµ{¦¡¼gN­Ó¨BÆJ¡A¦ý§A¥u¦³¬Y¨BÆJ¥d¦í¤£·|¡A¨ä¥L¥\¯à§A³£¥i¥H¦Û¤v¼g¥X¨Ó

¥u­n§â§A¤£·|ªº³¡¤À¡A³æ¿W®³¥X¨Óµo°Ý§Y¥i¡A¤§«á§A´N¥i¥H²Õ¦X¥X§A­nªº§¹¾ã¥\¯à

¤p¨ì·s¼W¤u§@ªí¡B½Æ»s1¦C¸ê®Æ¡A³£¥i¥H·í§@¨BÆJ

§Ú­Ì¦b¸Ñ»Ý¨Dªº®É­Ô¡A¤]¬O·|¥ý·Q¾ãÅé¾Þ§@¨BÆJ(µ{¦¡¥D¬[ºc)

¦A¤@­Ó­Ó§¹¦¨¤@¶}©l·Qªº¨BÆJ¡A§A»Ý­n°µªº¥u¬O¡A§â§A­ì¥»"¤â°Ê"¾Þ§@ªº³¡¤À

¤À¨BÆJ¤@­Ó­Ó¦C¥X¨Ó¡A¦A¸ÕµÛ¼g³o¨Ç¨BÆJ¡A¼g¤£¥X¨Óªº³¡¤À¡A´Nµo°Ý

¥H¤UÁ|¨Ò»¡©ú~


°²¦p§Aªº»Ý¨D»Ý­nµ{¦¡°µ10­Ó¨BÆJ¡A¦ý§A¥u¦³¨BÆJ6¥d¦í¤£·|

³æ¿W§â¨BÆJ6®³¥X¨Óµo°Ý¡A°Ý¨ìµª®×¤§«á¡A¦Û¤v²Õ¦X¥X§A­nªº¥\¯à

·íµM´ú¸ÕÀÉ­n¥ý¦Û¤v§¹¦¨¨BÆJ1~5(¤â°Êor¼gVBA)¡A¦Aªþ¨ì½×¾Â¤Wµo°Ý


Sub §¹¾ã¥\¯à¥Dµ{§Ç()
  Call «e­±·|ªº°Æµ{§Ç   '¨BÆJ1~5
  Call ¨BÆJ6
  Call «á­±·|ªº°Æµ{§Ç   '¨BÆJ7~10
End Sub

Sub ¨BÆJ6()
'¶·µo°Ý¡A¤£·|°µªº³¡¤À
End Sub

Sub «e­±·|ªº°Æµ{§Ç()
¨BÆJ1
¨BÆJ2
¨BÆJ3
¨BÆJ4
¨BÆJ5
End Sub

Sub «á­±·|ªº°Æµ{§Ç()
¨BÆJ7
¨BÆJ8
¨BÆJ9
¨BÆJ10
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2021-7-20 12:08 ½s¿è

¦^´_ 16# n7822123

n7822123«e½ú¦n  
¼Ó¥Dªº»Ý¨D¬O  
¦Û°Ê¶}±Ò«ü©wªº¬¡­¶Ã¯ÀÉ®×   ¦h«È¤á½LÂIªí
¤À§O¦³2­Ó¤u§@ªí 2ºØ®æ¦¡  
¤é´ÁÄæ¦ì BH~CF  ªº½d³ò¤§¶¡
"½LÂI" ¤u§@ªí (STEP¢±)¢à¢Ý¢å¸õ¢±®æ
"stock" ¤u§@ªí (STEP¢²)¢à¢Ý¢å¸õ¢²®æ

±NE4ªº¤é´Á»P DIÄæ¦ìªº¤é´Á°µ¤ñ¹ï
¦C¦p:   DI6 =  D+0  E4 ´N¦©¤@¤Ñ
            DI6 =  D+1 «h  «O«ù¤£ÅÜ
¦b¬Y¨Ç¦ì¸m¦³¬ï´¡±a¤½¦¡ªºÀx¦s®æ «O«ù¤£ÅÜ
­n¤ñ¹ïªº¶µ¥Ø ¦³ «È¤á «~¦W ÁÙ¦³¤é´ÁÄæ¦ì BH~CF  ªº½d³ò¤§¶¡
¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð
¶}±Ò­n¤ñ¹ïªºÀɮס@
³o¸Ì¤]¬O¥Î«ü©wÀɦWªº¤è¦¡¶}±Ò
¦C¦p¡G¦h«È¤á½LÂIªí¡@¢Ô¢Ý¢à¢Û¢Ï¢â¡]E4¡AÈТÒÈС^»P¡@¸U¹F®w¦sªí¡@¬Û¹ïÀ³ªº¤é´Á¤u§@ªí¢á¢Ö¢Ó¢Ó¢â¢á¡]¢æ¡^
¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð¡Ð
¸U¹F®w¦sªí¡@¬O¹ïÀ³¡@¦h«È¤á½LÂIªí¡@ªº¡@"stock" ¤u§@ªí (STEP¢²)¢à¢Ý¢å¸õ¢²®æ
¦]¬°¦³¢±ºØ®æ¦¡¤À§O¤¬¬Û¹ïÀ³¡@¡@
§ä¨ì¬Û¹ïÀ³ªº¤é´Á´N§â¼Æ­È¶K¤W¡]¦h«È¤á½LÂIªí¡^
§Ú²{¦b°ÝÃD¥X¦b­n¤ñ¹ïªºÀx¦s®æ¦ì¸m¨S¦³©T©w
¦C¦p¡G¸U¹F®w¦sªí
«~¦WÄæ¦ì¬O¦X¨Öª¬ºA
¥kÃä¡@¢ã¡ã¢Ï¢Ô¦ì¸m¤£©T©w¡@¤£¤@©w¬O¢ã¡ã¢Ï¢Ô¤]¦³¥i¯à¬O±q¢ä¶}©l©ÎµÛ¨ä¥¦Äæ¦ì¶}©l
¥Ø«eµo²{¡@¦³ÈнLÂIÈЦr¼Ëªº¦ì¸m¡@¥i¥H§ä¥X½d³ò¡@¦ý¤£ª¾¹D¦p¦ó°µ
½Ð°Ý«e½ú¡@­n«ç»ò§â¬Û¹ïÀ³ªº¡@«~¦W¡@»P¡@¤é´Á¡@ÁÙ¦³¤é´Á¤U¤èªº¼Æ­È¡@¥¿½Tªº©ñ¨ì°}¦C¡@¤è«K°µ¤ñ¹ï
¦pªGª½±µ¥þ³¡¶ë¨ì°}¦C¤£¦n°µ¤ñ¹ï¡@¦]¬°¤u§@ªíªº®æ¦¡¤º®e¤Ó¦h¦Ó¥B·|Åܧó½ÆÂø

TOP

¦^´_ 16# n7822123

©êºp«e½ú¡@§Ñ°Oªþ¤WÀÉ®×
0720.rar (504.38 KB)

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2021-7-20 15:21 ½s¿è

¦^´_ 18# °a¤ªºµ

µ²¦X§Aªº»¡©ú¡A¥H¤Î«e­±°Q½×¦ê¡A²×©ó¬ÝÀ´¤F
¨ä¹ê¥u¬OAÀÉAªíA®æ¦¡ ·h²¾¸ê®Æ¨ì BÀÉBªíB®æ¦¡
Ãø¦b¤£©T©wªí®æ¡A­n§ä»Ý­nªº¸ê®Æ½d³ò


¦]¬°¦³¢±ºØ®æ¦¡¤À§O¤¬¬Û¹ïÀ³¡@¡@
§ä¨ì¬Û¹ïÀ³ªº¤é´Á´N§â¼Æ­È¶K¤W¡]¦h«È¤á½LÂIªí¡^

¨Ó·½ªí½L®t¤U­±ªº XX½c¡A¥i¥H¥Î¦X¨ÖÀx¦s®æ"¦C¼Æ"(Columns.Count)¡A§PÂ_¬O2 Row ÁÙ¬O 3 Row

§Ú²{¦b°ÝÃD¥X¦b­n¤ñ¹ïªºÀx¦s®æ¦ì¸m¨S¦³©T©w
¦C¦p¡G¸U¹F®w¦sªí
«~¦WÄæ¦ì¬O¦X¨Öª¬ºA

§A»¡ªº¬O"EÄæ"ªº«~¦W§a
¦X¨ÖÀx¦s®æ¡A¥u¦³²Ä¤@®æ¦³¸ê®Æ¡A¨ä¾l³£¬OªÅ¥Õ
§Q¥Î¦¹ÅÞ¿è§PÂ_¦n¡A¬O¥i¥H³B²zªº
¦ý§Úµo²{ EÄæ »P BGÄæ ³£¦³«~¦W¡AÁÙ¤¬¬Û½Ä¬ð£½£¿




¥kÃä¡@¢ã¡ã¢Ï¢Ô¦ì¸m¤£©T©w¡@¤£¤@©w¬O¢ã¡ã¢Ï¢Ô¤]¦³¥i¯à¬O±q¢ä¶}©l©ÎµÛ¨ä¥¦Äæ¦ì¶}©l
¥Ø«eµo²{¡@¦³ÈнLÂIÈЦr¼Ëªº¦ì¸m¡@¥i¥H§ä¥X½d³ò¡@¦ý¤£ª¾¹D¦p¦ó°µ

¬Ý¤F¦Uªí®æ¡A½T¹ê¥u¯à¥Î"½LÂI"¨Ó½T©w½d³ò
¥»¨Ó·Q¥Î¤ñ¸û¤Ö¥Îªº¦r¼Ë"½L®t"¨Ó§ä½d³ò
¦ýµo²{¦³ªº¤é´Á¦b½L®t«á­±¡A¦³ªº¦b«e­±......(³o®æ¦¡¤£¬ì¾Çªü)¡A©Ò¥HÁÙ¬O½LÂI§a
°w¹ï³oºØ¤£©T©w½d³òªºªí®æ¡A¶·­n¥ý§ä¤@­Ó "¤£­«½Æ¡B°ß¤@" ªºÃöÁä¦r¨Ó"©w¦ì"
¬Ý¤F¤U¨Ó·½ªí®æ¡A½LÂI¦b¦P¤@±iªí®æ¤]¬O°ß¤@ªº¡A©Ò¥H¥i¥H¥Î
§Q¥ÎRangeª«¥óªº"Find"¤èªk¥i¥H§äÃöÁä¦rªºÄæ¦ì


½Ð°Ý«e½ú¡@­n«ç»ò§â¬Û¹ïÀ³ªº¡@«~¦W¡@»P¡@¤é´Á¡@ÁÙ¦³¤é´Á¤U¤èªº¼Æ­È¡@¥¿½Tªº©ñ¨ì°}¦C¡@¤è«K°µ¤ñ¹ï
¦pªGª½±µ¥þ³¡¶ë¨ì°}¦C¤£¦n°µ¤ñ¹ï¡@¦]¬°¤u§@ªíªº®æ¦¡¤º®e¤Ó¦h¦Ó¥B·|Åܧó½ÆÂø

¤W­±¨º¤@¨B½T»{½d³ò¤§«á¡A´N¥i¥H¶ë¶i°}¦C¤F
µ²¦X¦X¨ÖÀx¦s®æªºÅÞ¿è¡A¥u¦³²Ä¤@®æ¦³¸ê®Æ
©Ò¥H°_©lÄæ¥i§ä"½LÂI"¦r¼ËªºÄæ¦ì¡A²×ÂIÄæ¦ì¥i¥H§Q¥Î¸Ó¦X¨ÖÀx¦s®æªº"Äæ¼Æ"(Columns.Count)¨Ó­pºâ
°_ÂI¦C¬Ý°_¨ÓÆZ©T©wªº¡A³£±q²Ä7¦C¶}©l¡A©ÎªÌ§ä"½LÂI"¦C+2
²×ÂI¦C¬Ý°_¨Ó¥i¥H¥ÎAÄæ(«O¦s¤Ñ¼Æ)ªº¥½ºÝ¸ê®Æ¨Ó§PÂ_


¦n°Õ¡A§Aªº°ÝÃD§Ú³£¸Ñµª§¹²¦¤F
§ÚÁÙ¨S­n¶}©l¼g
§A¥ý¬D¾Ô¬Ý¬Ý§a~
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2021-7-20 19:55 ½s¿è

¦^´_ 14# PJChen

·PÁ¡@n7822123«e½ú´£ÂI¡@²{¦b¤w¸g¥i¥H©ñ¨ì°}¦C¤F¡@
¦X¨ÖÀx¦s®æªº"Äæ¼Æ"(Columns.Count)¤£ª¾¦p¦ó­pºâ¡@¡ÖÈСա@
§Ú¬O¥Î¡@End(2).Column - 1¡@ªº¤è¦¡¡@¤£ª¾¹D·|¤£·|¦³°ÝÃD¡@¥Ø«e¬Ý°_¨Ó¬OÁÙ¥i¥H
¦ý°j°é«oÅܱo§ó¦h¤F¨þ¨þ¡D¡D¥ý¤£ºÞ¤F¡@¥ý½ÐPJChen¤j¤jÀ°§Ú¸Õ¸Õ¬Ý¡@¢û¢ð¢í¢í¢ü¢û¡]"stock" ¡^³¡¤À¦³¨S¦³°ÝÃD¡@·PÁÂ

0720_1.rar (507.96 KB)

TOP

        ÀR«ä¦Û¦b : ¦³¦h¤Ö¤O¶q´N°µ¦h¤Ö¨Æ¡A¤£­n¤ß¦sµ¥«Ý¡Aµ¥«Ý¤~·|¸¨ªÅ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD