luffyzoro

¦^´_ 18# GBKEE


ª©¤j±z¦n
¦A¦¸ÁÂÁÂ
¦p¦¹¦h¦¸³Â·Ð±z,¯u¬O¤£¦n·N«ä

§Ú²{¦b´N¥u¦³¤@­Ó"b.xls"

1.    Workbooks(hubopf).Worksheets("AC8E").Activate
   
2.   Dim hubcn As Integer, hubrwcnt As Integer
   
3.    hubrwcnt = ActiveSheet.UsedRange.Rows.Count
   
4.   For hubcn = 2 To hubrwcnt - 1
   
5.     Workbooks(hubopf).Activate
   
6.     Worksheets("AC8E").Range("BT" & hubcn).Select
   
7.    If Selection.Value > 0 Then
   
8.       Do
   
9.         Workbooks(calopf).Worksheets("sheet2").Activate
   
10.         Cells(ActiveSheet.Rows.Count, 6).End(xlUp).Select
    
11.         Selection.Offset(1, 0).Select
    
12.         Dim mycarw As Integer
    
13.         mycarw = ActiveCell.Row
    
14.         ActiveCell.FormulaR1C1 = _
    
15.         "=IF(AND(RC[-1]=[" & hubopf & "]AC8E!R" & hubcn & "C2,[" & hubopf & "]AC8E!R" & hubcn & "C72>0,[" & hubopf & "]AC8E!R" & hubcn & "C72-SUM(R" & mycarw & "C11:RC[5])>0),[" & hubopf & "]AC8E!R" & hubcn & "C9)"
    
16.         Selection.Value = Selection.Value
    
17.         ActiveCell.Offset(1).Select
    
18.       Loop Until ActiveCell.Offset(-1).Value = False

½Æ»s¥N½X

¥H¤W¬O¤ù¬qªºµ{¦¡½X,
§Ú·Q½Ð±Ðª©¤j,§Ú©Ò·Q­nªºActiveCell.FormulaR1C1 À³¸Ó¬O¦bWorkbooks(calopf).worksheets("sheet2")ªºÀx¦s®æ¤§¤¤,¬O¤£¬O§Ú­þÃä¼g¿ù¤F,¾É­P¤½¦¡¨Ã¨S¦b§Ú­nªºÀx¦s®æ¤º?

©Î¬Oª©¤jÄ@·Nªº¸Ü,§Ú¥i¥H±NÀÉ®×±Hµ¹ª©¤jÀ°¦£¬Ý¤@¤U>"<

·PÁÂ

GBKEE

¦^´_ 17# luffyzoro
½ÐÀˬd¨C¤@  Workbooks (hubopf) ¤¤¬O§_ ¦³¤u§@ªí¦WºÙ"AC8E"
=[" & hubopf & "]AC8E!R" & hubcn & "C2
³o¤½¦¡ hubopf  ·|¬O "b.xls" ."c.xls" "d.xls"  ¤£¤@©wªºÀÉ®× ,  AC8E »Ý¬O³o¨ÇÀɮפ¤¤@©w¦³ªº¤u§@ªí¦WºÙ

luffyzoro

¦^´_ 16# GBKEE

½ÐÀˬd¨C¤@  Workbooks (hubopf) ¤¤¬O§_ ¦³¤u§@ªí¦WºÙ"AC8E"

½Ð°Ýª©¤j³o¥y¸Üªº·N«ä¬O«ü¦b¤½¦¡¤¤ªºÁÙ¬O»¡§Ú«ü©wªº¸ê®Æ§¨¨C¤@Àɮ׳£­n¦³¤u§@ªí"AC8E"?

§Ú®³ª©¤j15¼Óªº¦^ÂФ½¦¡¶K¦^§Úªºµ{¦¡½X?¤´µM¥X²{¦P¼Ëªº¿ù»~°T®§

©Î¬O¸Ó¤½¦¡¦³¨S¦³¨ä¥Lªº¼gªk?¯u¤£ª¾¹D¬O­þ¸Ì¦³°ÝÃD?ÀY§ÖÃz¤F>"<

§ÚªºEXCELª©¥»¬O2003¦³®t¶Ü?

¦A¦¸·PÁÂ

GBKEE

¦^´_ 15# luffyzoro
9¼Óªº¿ù»~   ³o¸Ì·|¸õ¥X¹ï¸Ü®Ø­n¨D§Ú¿ï¾Ü¶}±ÒÀÉ®×,  ChDir "H:\My documents\±M®×\¤u§@¶q±M®×"    «á9¼Óªº¿ù»~®ø¥¢¤F

15¼Ó¿ù»~°T®§  °õ¦æ¶¥¬q¿ù»~'1004':À³¥Îµ{¦¡©Îª«¥ó©w¸q¤Wªº¿ù»~
³oµ{¦¡½X §Ú¥N¤JÅÜ¼Æ hubopf   , hubcn,  ¨S¦³¿ù»~,  ½ÐÀˬd¨C¤@  Workbooks (hubopf) ¤¤¬O§_ ¦³¤u§@ªí¦WºÙ"AC8E"
ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]=[" & hubopf & "]AC8E!R" & hubcn & "C2,[" & hubopf & "]AC8E!R" & hubcn & "C72>0,[" & hubopf & "]AC8E!R" & hubcn & "C72-SUM(R" & mycarw & "C11:RC[5])>0),[" & hubopf & "]AC8E!R" & hubcn & "C9)"

luffyzoro

¦^´_ 14# GBKEE
   For hubcn = 2 To hubrwcnt - 1
    Workbooks(hubopf).Activate
    Worksheets("AC8E").Range("BT" & hubcn).Select
   If Selection.Value > 0 Then
      Workbooks(calopf).Worksheets("sheet2").Activate
      Dim mycarw As Integer
      mycarw = ActiveCell.Row    Do
       ChDir "H:\My documents\±M®×\¤u§@¶q±M®×"        
       ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]=[" & hubopf & "]AC8E!R" & hubcn & "C2,[" & hubopf & "]AC8E!R" & hubcn & "C72>0,[" & hubopf & "]AC8E!R" & hubcn & "C72-SUM(R" & mycarw & "C11:RC[5])>0),[" & hubopf & "]AC8E!R" & hubcn & "C9)"
        Selection.Value = Selection.Value
        ActiveCell.Offset(1).Select
      Loop Until ActiveCell.Offset(-1).Value = False

·PÁª©¤j
¦ý¬OÁÙ¬O¤@¼Ë¥X°ÝÃD
¿ù»~°T®§  °õ¦æ¶¥¬q¿ù»~'1004':À³¥Îµ{¦¡©Îª«¥ó©w¸q¤Wªº¿ù»~

¤£¦n·N«ä°Ú,ª©¤j
ÂŦ⨺¤@¬q§Ú¬ÝÀx¦s®æ³£¦³¦bhubopf©Ò¥NªíªºÀɮײ¾°Ê
¥i¬O¨ì¤F¤½¦¡¨º¤@¬q´N¥X¿ù¤F
ÁٽбzÀ°¦£¬Ý¤@¤U¬O§_ÁÙ¦³­þÃ䦳°ÝÃD

GBKEE

¦^´_ 13# luffyzoro
8¼Ó: °²³]§Úªº¥¨¶°¬O¼g¦bA.xls , ¥ÎA.xls¶}±ÒB.xls , ·Óª©¤jªº¤è¦¡§Úªºhubopf·|±o¨ì"D:\C¸ê®Æ§¨\F¸ê®Æ§¨\B.xls"

hubopf ="B.xls"
[" & hubopf & "]¦p¥Ø«eºÏºÐ¾÷ªº¸ô®|¤£¬O D:\C¸ê®Æ§¨\F¸ê®Æ§¨  Excel·|§ä¤£¨ìÀɮתº  
½Ð§A¦b¾A·íªº¦a¤è¥[¤J   ChDir  "D:\C¸ê®Æ§¨\F¸ê®Æ§¨"    ²¾°Ê¥Ø«eºÏºÐ¾÷ªº¸ô®|

luffyzoro

¦^´_ 12# GBKEE


    ·PÁÂGBKEE¤j¤j¨³³t¦^ÂÐ

§ÚÁÙ¨Sµo°Ý«e¤w¦³§ï¹Lª©¤j©Ò±Ðªº¤è¦¡,¦ýÁÙ¬O°õ¦æ¿ù»~,¹ê¦b¬O¨S»³¤F,¥u¦n«pÁy¥Öªº¤@ª½¨Ó³oÃä½Ð±Ð

:L

GBKEE

¦^´_ 10# luffyzoro
§ï¦¨ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]=[" & hubopf & "]AC8E!R" & hubcn & "C2,[" & hubopf & "]AC8E!R" & hubcn & "C72>0,[" & hubopf & "]
AC8E!R" & hubcn & "C72-SUM(R" & mycarw & "C11:RC[5])>0),[" & hubopf& "]AC8E!R" & hubcn & "C9)"

ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]=[hubopf]AC8E!R" & hubcn & "C2,[hubopf]AC8E!R" & hubcn & "C72>0,[hubopf]AC8E!R" & hubcn & "C72-SUM(R" & mycarw & "C11:RC[5])>0),[hubopf]AC8E!R" & hubcn & "C9)"
§Aª¾¹D hubcn ¬OÅܼƭn¦p¦¹ªº¥[¤JÅܼƠ & hubcn &
¦P²z hubopf ¬OÅÜ¼Æ ¤]­n¦p¦¹°µªº

luffyzoro

¦^´_ 9# GBKEE

1. Sub Test()
   
2.     Dim hubopf As String, AR
   
3.     hubopf = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
   
4.     If hubopf = "False" Then MsgBox "½Ð¿ï¨úÀÉ®×": Exit Sub
   
5.     AR = Split(hubopf, "\")
   
6.     hubopf = AR(UBound(AR))   'hubopf = "B.xls"ÀHµÛ¶}±ÒÀɦW¤£¦P¤]¥i¯à¬O"C.xls"
   
7.     Workbooks.Open hubopf     'Workbooks( hubopf )
   
8.     Worksheets("AC8E").Cells.Select
   
9.     Worksheets("AC8E").Range("A:BY").Sort Key1:=Worksheets("AC8E").Range("D2"), _
   
10.     Order1:=xlAscending, Key2:=Worksheets("AC8E").Range("I2") _
    
11.     , Order2:=xlAscending, Header:=xlYes
    
12.     ThisWorkbook.Activate
    
13.     Dim calopf As String
    
14.     calopf = ThisWorkbook.Name
    
15.     ActiveSheet.Cells.Select
    
16.     Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("C2"), _
    
17.     Order2:=xlAscending, Key3:=Range("A2"), _
    
18.     Order3:=xlAscending, Header:=xlYes
    
19.     Cells(ActiveSheet.Rows.Count, 6).End(xlUp).Select
    
20.     Selection.Offset(1, 0).Select
    
21.     Workbooks(hubopf).Worksheets("AC8E").Activate
    
22.   Dim hubcn As Integer, hubrwcnt As Integer
    
23.    hubrwcnt = ActiveSheet.UsedRange.Rows.Count
    
24.   For hubcn = 2 To hubrwcnt - 1
    
25.     Workbooks(hubopf).Activate
    
26.     Worksheets("AC8E").Range("BT" & hubcn).Select
    
27.    If Selection.Value > 0 Then
    
28.       Workbooks(calopf).Worksheets("sheet2").Activate
    
29.       Dim mycarw As Integer
    
30.       mycarw = ActiveCell.Row
    
31.       Do
    
32.         ActiveCell.FormulaR1C1 = _
    
33.         "=IF(AND(RC[-1]=[hubopf]AC8E!R" & hubcn & "C2,[hubopf]AC8E!R" & hubcn & "C72>0,[hubopf]AC8E!R" & hubcn & "C72-SUM(R" & mycarw & "C11:RC[5])>0),[hubopf]AC8E!R" & hubcn & "C9)"
    
34.       Selection.Value = Selection.Value
    
35.         ActiveCell.Offset(1).Select
    
36.       Loop Until ActiveCell.Offset(-1).Value = False

½Æ»s¥N½X

´N¬O³o¼Ë¤lµM«á¨ì¤½¦¡®É´N¤@ª½­n­«¶}ÀÉ®×

luffyzoro

¦^´_ 9# GBKEE

·PÁÂGBKEEª©¤j
Dim hubopf As String, AR
    hubopf = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    If hubopf = "False" Then MsgBox "½Ð¿ï¨úÀÉ®×": Exit Sub
    AR = Split(hubopf, "\")
    hubopf = AR(UBound(AR))   'hubopf = "B.xls"ÀHµÛ¶}±ÒÀɦW¤£¦P¤]¥i¯à¬O"C.xls"
    Workbooks.Open hubopf     'Workbooks( hubopf )

¦p±zªº¨ó§U±N¤W­±ªºµ{¦¡½X¨Ï¥Î¤§«á,³v¦æ°õ¦æ¨ì
ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]=[hubopf]AC8E!R" & hubcn & "C2,[hubopf]AC8E!R" & hubcn & "C72>0,[hubopf]AC8E!R" & hubcn & "C72-SUM(R" & mycarw & "C11:RC[5])>0),[hubopf]AC8E!R" & hubcn & "C9)"

³o¸Ì·|¸õ¥X¹ï¸Ü®Ø­n¨D§Ú¿ï¾Ü¶}±ÒÀÉ®×,¦pªG¤£¿ïÀÉ®×,¦bÀx¦s®æ´N·|§P§OFALSE,¿ï¤FÀɮפ~·|¥X²{§Ú­nªºµ²ªG
§Úµo²{¬Ohubopf¨S¦³­È,©Ò¥H¤S­n¨D­«¶}ÀÉ®×,¥i¬O³o­Óhubopf¬O¤@¶}©l´N«Å§iªº,¨ì¤F¤½¦¡®É«o¤S»Ý­«¶}Àɮפ~¦³­È,
§Úªº¥¨¶°¬O¼g¦bA.xlsªºsheet2 , ¦b¤W­z¤½¦¡°õ¦æ«e¦³¦h¦¸¤Á´«A.xls ©M B.xls , ³o­Ó¤½¦¡¬O¦bA.xlsªºÀx¦s®æ¤¤,
½Ð±Ð¤j¤j,¦p¦ó¤~¯à¤£­n«ùÄò­«¶}ÀÉ®×?

·PÁÂ