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

[µo°Ý] ½Ð°Ý¦p¦ó§Ö³t¤ñ¹ï¸ê®Æ

[µo°Ý] ½Ð°Ý¦p¦ó§Ö³t¤ñ¹ï¸ê®Æ

Dear ¤j¤j,

¦³½ÐÂI°ÝÃD·Q½Ð±Ð¦U¦ì.
(1)
B = Sheets("B¸ê®Æ®w").Range([A2], [A65536].End(xlUp))
For Each A In B
if A = 1101 Then
  ............(¬Ù²¤)
End If
Next A

µ{¦¡·|¤ñ¹ïA2 ~ A65536¤º ©Ò¦³ªºÀx¦s­È , ¤£¹L¦³¨S¦³¤°»ò¤è¦¡¥i¥HÅý·íµ{¦¡¤@¤ñ¹ï¨ìif±ø¥ó¦¨¥ß®É , ´N¤£¥ÎÄ~Äò¤ñ¹ï«á­±ªºÀx¦s®æ¤F?

(2)
¥Hªþ¥ó½d¨Ò¬°¨Ò.
¦bsheet1[c1]¿é¤JªÑ¸¹ , sheet1[AE2]&[AC2]¿é¤J·j´M¦~¤À½d³ò«á , µM«á¥h¤ñ¹ïsheet2 , ¦A±N¤ñ¹ï¦¨¥\ªº¸ê®Æ½Æ»s¨ìsheet1[ACx : AIx].
¥Hªþ¥ó¬°¨Ò , shee2[a12]¬O¥xªd¤@¤ë¥÷ , ÁöµM§Ú­Ì¤wª¾¤G¤ë¥÷¥xªd¬O[a12].offset(0, 10) , ¤£¹LÃø§K·|¦³·sªÑ¸¹¥[¤J , ©Ò¥H·j´M¤U­Ó¤ë¥÷®ÉÁÙ­«ÀY¶}©l·j´M°_ , ¤£¹L¦Ü¤Ö¨C­Ó¤ë¥÷ªº¸ê®Æ³£¬O¹j10®æ

¤p§Ì¥HFor Each«ü¥O¥i¥H°µ¨ìÃþ¦üªº¥\¯à , ¤£¹L¥u¯à¦¨¥\½Æ»s²Ä¤@­Ó¤ë¥÷ , ¦Ó¥B³t«×«ÜºC.
·Q½Ð±Ð¦U¦ì¥ý¶i¦³¨S¦³¨ä¥¦«ü¥O¤Î¤°»ò¤è¦¡¥i¥H°µ¨ì³o¼Ëªº¥\¯à.

°]³ø_1.zip (732.12 KB)

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-3-9 23:51 ½s¿è

¦^´_ 1# chen255
¥i¨Ï¥ÎFIND§ä¨ì¦C¦ì
¦ý¬Ý¤£À´§AªºÄæ¦ì¹ïÀ³
¦pªG°õ¦æµ²ªG¦³»~¡A½Ð¥Hªþ¥ó¤¤ªº½d¨Ò¶ñ¤JSheet1¨Ó»¡©ú
  1. Sub nn()
  2. Dim A As Range, B As Range
  3. With Sheet1
  4. For Each A In .Range(.[AB4], .[AB4].End(xlDown))
  5.    ym = Val(Split(A, "/")(0)) & "¦~" & Val(Split(A, "/")(1)) & "¤ë"
  6.    Set B = Sheet2.Rows(1).Find(ym, lookat:=xlPart)
  7.    Set c = B.EntireColumn.Find(.[C1])
  8.    A.Offset(, 1).Resize(, 7).Value = Array(c.Offset(, 2), c.Offset(, 5), c.Offset(, 4), c.Offset(, 6), c.Offset(, 7), c.Offset(, 8), c.Offset(, 9))
  9. Next
  10. End With
  11. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ register313 ©ó 2012-3-10 14:51 ½s¿è

¦^´_ 1# chen255
  1. Sub ¤ëÀ禬©ú²Ó()
  2. With Sheets("Main")
  3.   .[AB1].CurrentRegion.Offset(3, 0) = ""
  4.   For Y = .[AC2] To .[AE2]
  5.     For M = 1 To 12
  6.       Sheets(Y & "Àç·~¦¬¤J").Activate
  7.       Set Source = Sheets(Y & "Àç·~¦¬¤J").Range([A12].Offset(0, (M - 1) * 10), [J65536].Offset(0, (M - 1) * 10).End(xlUp))
  8.       Set Code = Source.Find(.[C1], , , xlWhole)
  9.       R = .[AB65536].End(xlUp).Row + 1
  10.       If Not Code Is Nothing Then
  11.         .Range("AB" & R) = Y & "/" & Format(M, "00")
  12.         .Range("AC" & R, "AI" & R) = Array(Code.Offset(, 2), Code.Offset(, 5), Code.Offset(, 4), Code.Offset(, 6), Code.Offset(, 7), Code.Offset(, 8), Code.Offset(, 9))
  13.       End If
  14.     Next M
  15.   Next Y
  16. End With
  17. End Sub
½Æ»s¥N½X

TOP

³o´X¤Ñ ¤u§@¶q«Ü¤j ¨ì²{¦b¤~¤W¨Ó¬Ý©« ,
³o¸Ì¥ý·PÁÂHsieh & register313 ¨â¦ì¥ý¶i , ¤p§Ì±ß¤@ÂI¦A±N´ú¸Õµ²ªG¶K¤W¨Ó , ÁÂÁÂ.

TOP

¦^´_ 2# Hsieh

¤p§Ì±ß¤@ÂI¦A±N´ú¸Õµ²ªG¶K¤W¨Ó , ÁÂÁÂ.

TOP

¦^´_ 3# register313


    ¤p§Ì±ß¤@ÂI¦A±N´ú¸Õµ²ªG¶K¤W¨Ó , ÁÂÁÂ.

TOP

¦^´_ 3# register313

Dear register313¤j¤j

§A¦n , ¦³­Ó°ÝÃD·Q½Ð±Ð.

Sheets(Y & "Àç·~¦¬¤J").Activate

¬°¦ó»Ý­n°õ¦æ³o¬q°Ê§@.

TOP

¥»©«³Ì«á¥Ñ register313 ©ó 2012-3-18 02:12 ½s¿è

¦^´_ 7# chen255

Sheets(Y & "Àç·~¦¬¤J").Activate
Set Source = Sheets(Y & "Àç·~¦¬¤J").Range([A12].Offset(0, (M - 1) * 10), [J65536].Offset(0, (M - 1) * 10).End(xlUp))
Sheets(Y & "Àç·~¦¬¤J").¬O¦hªº,¥i¤£­n
©w¸q¤u§@ªí¤§Àx¦s®æ½d³ò®É,¸Ó¤u§@ªí¥²¶·¬°¬¡°Ê¤u§@ªí

¥H¤W¤G¦æ¤]¥i§ï¬°
Set source = Sheets(Y & "Àç·~¦¬¤J").Range(Sheets(Y & "Àç·~¦¬¤J").[A12].Offset(0, (M - 1) * 10), Sheets(Y & "Àç·~¦¬¤J").[J65536].Offset(0, (M - 1) * 10).End(xlUp))

http://forum.twbts.com/viewthread.php?tid=1294&highlight=SET

TOP

¦^´_ 8# register313

¤F¸Ñ , ¦A¦¸¤j¤jªº·PÁ»¡©ú.

¥t¥~ , ·Q½Ð±Ð¥t¤@­Ó°ÝÃD.
°²³]§ÚµwºÐ¤º¦³1.html , 2.html , 3.html , 4html , 5.html ¦@¤­µ§ÀÉ®×.
¦³¿ìªk±NÀɮ׶פJ¨ì·Q«ü©wªºexcel ¦ì¸m¶Ü?

TOP

¦^´_ 9# chen255

excelÀÉ »P ­n¶×¤JªºhtmlÀÉ ©ñ¦P¤@¥Ø¿ý¤U
  1. Sub ¶×¤J()
  2. Dim i%
  3. Application.ScreenUpdating = False
  4. For i = 1 To 5
  5.   sFile = ThisWorkbook.Path & "\" & i & ".html"
  6.   If Dir(sFile) = "" Then GoTo NEXT_FILE
  7.      For Each Sh In Sheets
  8.          If Sh.Name = i Then
  9.             Application.DisplayAlerts = False
  10.             Sh.Delete
  11.             Application.DisplayAlerts = True
  12.          End If
  13.      Next
  14.      Sheets.Add After:=Sheets(Sheets.Count)
  15.      ActiveSheet.Name = i
  16.      Workbooks.Open Filename:=ThisWorkbook.Path & "\" & i & ".html"
  17.      Workbooks(i & ".html").Sheets("sheet1").Cells.Copy ThisWorkbook.ActiveSheet.[A1]
  18.      Workbooks(i & ".html").Close 2
  19. NEXT_FILE:
  20.    Next
  21. Application.ScreenUpdating = True
  22. MsgBox "¶×¤J§¹¦¨"
  23. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD