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

[µo°Ý] ½Ð°Ý¸Ó¦p¦ó¤ñ¹ï°}¦C¤¤ªº¸ê®Æ»P¤u§@ªí©m¦W ?

[µo°Ý] ½Ð°Ý¸Ó¦p¦ó¤ñ¹ï°}¦C¤¤ªº¸ê®Æ»P¤u§@ªí©m¦W ?

¦U¦ì«e½ú¦n¡A

¤p§Ì·Q§Q¥Î vba¨C¤Ñ¦¬½L«á§ó·sopªº»ù®æ©M¥¼¥­­Ü¸ê®Æ¡A¦ý¬O¦b°õ¦æ¤W¾D¨ì¤@¨Ç°ÝÃD¡AÁٽЦU¦ìÀ°¦£¸Ñµª¡A§Úªº°ÝÃD¦p¤U:(¼Ò²Õ¤T¡A·Ð½Ð°Ñ¾\ªþ¥ó)¡C

§Ú¥ý¥Î¥~³¡³sµ²§ó·s¥x´Á©Òªº¸ê®Æ¡AµM«á´£¨ú¥X¨ÓÀx¦s¨ì°}¦CA¸Ì­±¡A¤§«á¦A§Q¥Î°j°é§âÀx¦s·Ó¶¶§Ç¦s¤J«ü©wªº¤u§@ªí©MÀx¦s®æ¡A¥X°ÝÃDªº¦a¤è¦³¨â­Ó:

1.·í§Ú§â©Ò¦³¸ê®Æ¦s¤J°}¦C¡A§Ú­n¥ý§PÂ_¬Û¹ïÀ³ªº¤u§@ªí¬O§_¤w¦s¦b(¤ñ¦p»¡5600¼i¬ù»ù)¡A©Ò¥H§Ú¼g¤F¤@­Ó¤pªº°j°é§PÂ_
  1. For iM = 1 To isheetsnumber  'small loop to test if the worksheet exists, exists =true otherwise false
  2.             If ThisWorkbook.Sheets(iM).Name = varA(I, 1, 8) Then
  3.                 Sheets_Exist = True:Exit For
  4.             Else
  5.                 Sheets_Exist = False
  6.             End If
  7.      Next iM
½Æ»s¥N½X
³oÃ䪺varA(I,1,8)´N¬O§Úªº°}¦C¤§¤@(§ó¥~­±ÁÙ¦³¤@­ÓIªº For °j°é)¡A¦ý¤£ºÞ§Ú«ç»ò´ú¦¡¡A¥X¨Óªº³£¬OFalse

2.²Ä¤G­Ó°ÝÃD¬O¡A°²³]¥i¥H§ä¨ì³o­Ó¤u§@ªí¡A§Ú·Q­n§â¹ïÀ³ªº¸ê®Æ¥Ñ°j°é¤º¶ñ¶i¥h¡A©Ò¥H§Ú­n¡i«ü©w³o­Ó¦W¦r¡jªº¤u§@ªí¡A¤£ª¾¹D¸Ó«ç»ò°µ?
§Ú­ì¥ý«Ü¥Õ誺¥H¬°¥i¥H¥Î
With Sheets("varA(I,1,8)")
¦ý¬O¦n¹³¤£¦æ¡A½Ð°Ý³oºØ±¡ªp¸Ó«ç»ò³B²z©O?

·Ð½Ð¦U¦ì«e½ú°Ñ¦Òªþ¥ó OP_OI.rar (105.7 KB)

ÁÂÁ¦U¦ìªºÀ°¦£:D
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

¦^´_ 1# j1221

1. ¦pªG¥u·|¶}¤@­Ó Excel ÀÉ, «h¤£¥Î¥´ ThisWorkbook, ­Y©È¦P®É¥i¯à¶}±Ò¶W¹L¤@­Ó Excel ÀÉ®×,
   «h½Ð©ó¶}ÀɮɴNª½±µ³]©w«ü¼Ð¨ì¸ÓÀÉ,§_«h­Y¨Ï¥Î ThisWorkBook ÁÙ¬O¥i¯à·|§ì¨ì¥t¤@­ÓÀɮתº¤º®e.
¨Ò¦p :
¦b Module ¸Ì¥[¤W
Dim vOP_OI

¦A¦b Workbook_Open ¸Ì¥[¤W
set vOP_OI=ThisWorkbook

¤Þ¥Î®É«h¥i¥Î vOP_OI.Sheets(2).Name ¨Ó§ì¨ú Sheet ªº Name

2. ­n¤Þ¥Î Sheet ªº¦W¦r®É, Variant ¥~¤£¥Î¦A¥[ ""


¦]¬°§AªºÀɮקڳo¸Ì¤£¯à¥¿±`°õ¦æ, ©Ò¥H§Ú°w¹ï§A©Ò»¡ªº°ÝÃDÂI,¥t¥~¥Î¤@­Óµ{¦¡¨Ó¼ÒÀÀ,§A°Ñ¦Ò¬Ý¬Ý :
  1. Private Sub Workbook_Open()
  2.   Dim iI%, iJ%
  3.   Dim vShtName(10, 2, 8)
  4.   
  5.   Set vOP_OI = ThisWorkbook
  6.   
  7.   iJ = Sheets.Count
  8.   For iI = 1 To iJ
  9.     vShtName(iI, 2, 8) = Sheets(iI).Name
  10.     Debug.Print Sheets(iI).Name = vShtName(iI, 2, 8)
  11.   Next iI
  12.   
  13.   For iI = 1 To iJ
  14.     Sheets(vShtName(iI, 2, 8)).Cells(1, 1) = Sheets(vShtName(iI, 2, 8)).Cells(1, 1) + iI
  15.   Next iI
  16.   
  17.   MsgBox vOP_OI.Sheets(2).Cells(1, 1)
  18. End Sub
½Æ»s¥N½X
OP_OI_test.zip (7.48 KB)
½Ð¶i¥¨¶°ªº½s¿è¾¹«á«ö F8 ³æ¨B°õ¦æ Æ[¹î¬ÛÃöÅܼƲ§°Ê±¡§Î, §Æ±æ¯à¹ï§A¦³©ÒÀ°§U.

TOP

¦^´_ 1# j1221
If ThisWorkbook.Sheets(iM).Name = varA(I, 1, 8) & "" Then
¤u§@ªí¦WºÙ¬O "¦r¦ê", varA(I, 1, 8) ¬O¼Æ¦r  & ""   ³sµ²¦Û²Å¸¹ÂàÅܬ°¦r¦ê
ªþÀÉ Private Sub Workbook_Open()  
    Sheets("TXF").[B7:P18007] = ""   ¨S³o¤u§@ªí

©Ò¦³ªº Module ¤@¯ë¼Ò²Õ ¥u¥i¥H¦³¤@­Ó Sub Auto_Open()

TOP

¦^´_ 2# luhpro


ÁÂÁÂluhpro¤j¡A¦h¾Ç¤F¤@­ÓDebug.Print ¤èªk¡A¤]ÁÂÁ±zªºªþ¥ó¡A§Ú¦A¬ã¨s¬Ý¬Ý¡I:D
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

TOP

¦^´_ 3# GBKEE

·PÁÂGBKEE¤j¸Ñ´b¡A§Ú¤@ª½¤£ª¾¹D¸Ó«ç»ò°µ¡A­ì¨Ó¦b§À¤Ú¥[¤J¡®""´N¥i¥H¡I¡I

¤]¾Ç¨ì­ì¨Ó¥u¯à¦³¤@­ÓModule¥i¥H¥Îsub auto_open()
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD