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

[µo°Ý] ½Ð°Ý¦p¦ó¤À§O¼¶¼gVBA¡A¨Ï¯S©w¤u§@ªíªº¯S©w­È¦X¨Ö¡A¦A§ì¯S©w­È¦^­ì©lÀÉ

[µo°Ý] ½Ð°Ý¦p¦ó¤À§O¼¶¼gVBA¡A¨Ï¯S©w¤u§@ªíªº¯S©w­È¦X¨Ö¡A¦A§ì¯S©w­È¦^­ì©lÀÉ

¥»©«³Ì«á¥Ñ sax868 ©ó 2012-5-10 16:49 ½s¿è

Notes adding.zip (221.94 KB)

¦U¦ì°ª¤â¤j¤j¦n:

½Ð°Ý¥H¤U­n¦p¦ó¤À§O¼¶¼gVBA? ¦]¬°¤u§@ªí¤@¦@¦³¤@¦Ê¦h­Ó¡A¤Ñ¤Ñ¤â°Ê§ï¦A¾ã²z¸ê®Æ§Ö­ú¤F...«ô°U~±Ï§Ú!!!

1)
¦b³o­ÓÀɮ׸̫إߤ@­Ó·sªº¤u§@ªíºÙ¡¨Updated Data¡¨¥B¸ê®Æ¥þ¬O"­È"´N¦n¡A¥i¥H¤£­n¥]§t­ì¨Ó¸ÓÄæ¦ì¥i¯à¦³ªº¨ç¼Æ¶Ü?
AÄæ­È¬O¨C±i¤u§@ªíªº C1 (°£¤F¨â±i¤u§@ªí¥H¥~,¨ä¦WºÙ¬° ¡§DATA¡¨ ¤Î¡¨Currency¡¨), ¥BA1ªº­È¬O²Ä¤@±i¤u§@ªíªºB1
BÄæ­È¬O¨C±i¤u§@ªíªº C2 (°£¤F¨â±i¤u§@ªí¥H¥~,¨ä¦WºÙ¬° ¡§DATA¡¨ ¤Î¡¨Currency¡¨), ¥BB1ªº­È¬O²Ä¤@±i¤u§@ªíªºB2
CÄæ­È¬O¨C±i¤u§@ªíªº E1 (°£¤F¨â±i¤u§@ªí¥H¥~,¨ä¦WºÙ¬° ¡§DATA¡¨ ¤Î¡¨Currency¡¨), ¥BC1ªº­È¬O²Ä¤@±i¤u§@ªíªºD1
D1¨ìBE1ªº­È¬O²Ä¤@±i¤u§@ªíªºA11¨ìBB11
D2¨ìBE2 ¥H¤U¨ì©³³¡ªº­È¬O¨C¤@±iworksheet¸Ìªº A12¨ìBB12¨ì©³³¡ (°£¤F¨â±i¤u§@ªí¥H¥~,¨ä¦WºÙ¬° ¡§DATA¡¨ ¤Î¡¨Currency¡¨)

2)
¸Ó±i¤u§@ªíAI12¨ì©³³¡ªº­È¬O °²³] $A12¡B$J12 ¤Î¡¨©T©w$C$1¡¨ ¤TªÌªº­È»P ¡¨Updated Data¡¨ ¤u§@ªí¸ÌAÄæ DÄæ MÄæ ¤TªÌ¬Ò¬Û²Å®É«h­È¬°Updated Data¤u§@ªí¸ÌªºAKÄæ
(§Ú¦³¥Î¨ç¼Æ¼gIF(AND+AND+AND) ¤£¹L³o¦³®ÉÆF¥ú¦³®É¤£¦æ,¤]¤£ª¾¹D¬°¬Æ»ò...)

·P¿E¤£ºÉ!

®z¤k¤l¯d

¦^´_ 1# sax868

¤£¤F¸Ñ 2) ¤§»¡©ú
  1. Sub xx()
  2. Sheets("Updated Data").Select
  3. Cells = ""
  4. [A1] = Sheets("AB1").[B1]:  [B1] = Sheets("AB1").[B2]:  [C1] = Sheets("AB1").[D1]
  5. [D1:BE1] = Sheets("AB1").[A11:BB11].Value
  6. For Each Sh In Sheets
  7.   If Sh.Name = "Currency" Or Sh.Name = "DATA" Or Sh.Name = "Updated Data" Then Exit For
  8.   R = 0: C = 0
  9.   R = Sh.[A11].End(xlDown).Row: C = C + (R - 12 + 1)
  10.   Ar = Sh.Range("A12:BB" & R).Value
  11.   [D65536].End(xlUp).Offset(1, 0).Resize(C, 54) = Ar
  12.   Br = Array(Sh.[C1], Sh.[C2], Sh.[E1])
  13.   [A65536].End(xlUp).Offset(1, 0).Resize(C, 3) = Br
  14. Next
  15. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# register313

·PÁÂregister313 ¤j¤jªº°T³t¦^ÂÐ!
¥i¬O¶]¤£¥X¸ê®Æ­C~½Ð°Ý³o¸òª©¥»¦³Ãö¶Ü? §Ú¬O¥Îoffice2010, W7.
¥¦Åã¥Ü¤@­Óµøµ¡¼g: Can't execute code in break mode
§Ú´N¦Û¤v¥[¶}¤@­Ó¤u§@ªíÁä¤JUpdated Data µM«á¦A¶]¤@¹M µ²ªG¤S¥X²{: Run-time error '9': Subscript out of range
«ô°U±Ï§Ú...

TOP

¦^´_ 3# sax868
½T¹êµLªk¤F¸Ñ§Aªº»Ý¨D
°õ¦æ¥H¤Uµ{§Ç¡A¦Û°Ê¥Í¦¨·s¤u§@ªí¡A±o¨ìUpdated Dataªº¸ê®Æ
¦A¨Ó°Q½×§Aªº²Ä2­Ó°ÝÃD
  1. Sub Ex()
  2. Dim Sh As Worksheet, Ar()
  3. For Each Sh In Sheets
  4. With Sh
  5.   If UBound(Filter(Array("Currency", "DATA", "Updated Data"), .Name, True)) < 0 Then
  6.   ReDim Preserve Ar(57, x)
  7.   If IsEmpty(Ar(0, 0)) Then
  8.      Ar(0, x) = .[B1].Value: Ar(1, x) = .[B2].Value: Ar(2, x) = .[D1].Value
  9.      s = 3
  10.      For Each a In .[A11:BB11].Value
  11.         Ar(s, x) = a
  12.         s = s + 1
  13.      Next
  14.      x = x + 1
  15.    End If
  16.    r = 12
  17.    Do Until .Cells(r, 1) = ""
  18.       ReDim Preserve Ar(57, x)
  19.          Ar(0, x) = .[C1].Value: Ar(1, x) = .[C2].Value: Ar(2, x) = .[E1].Value
  20.          s = 3
  21.          For Each a In .Range(.Cells(r, "A"), .Cells(r, "BB")).Value
  22.             Ar(s, x) = a
  23.             s = s + 1
  24.          Next
  25.          x = x + 1: r = r + 1
  26.    Loop
  27.   
  28.   End If
  29. End With
  30. Next
  31. With Sheets.Add(after:=Sheets(Sheets.Count))
  32. .[A1].Resize(x, 57) = Application.Transpose(Ar)
  33. End With
  34. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh

¯u¤£¦n·N«ä~­º¥ý¡A·PÁÂHsieh¤j¤jªºÀ°¦£! ¦b³oÀÉ®×¥i¥H¥Î¦ý¬O¨ì§ÚªºÀɮ׶]®É¥¦¥X²{ Run-time error '13': Type mismatch
½Ð°Ý³o¬O¬Æ»ò©O?

¦Û°Ê­°¯Å¦¨¥®¸X¶é¤j¯Zªº
®z¤k¤l¯d

TOP

¦^´_ 5# sax868
©êºp!¤S¬O§Ú...
§Ú­è­è¦³¸ÕµÛ§â .[A1].Resize(x, 57) = Application.Transpose(Ar)  ³o¤@¦æ®³±¼.µ²ªG¶}¤F¤@­Ó·sªº¸ê®Æªí¦ý¤º®e¬OªÅ¥Õ...

TOP

¦^´_ 6# sax868


    ³o¤@¦æ¬O¼g¤J­Èªº¤èªk
¥X²{¿ù»~¥i¯à¬OX=0§a
§â¥X²{¿ù»~ªºÀɮפW¶Ç¬Ý¬Ý
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Desktop.zip (142.95 KB) ¦^´_ 7# Hsieh

ÁÂÁÂHsieh¤jªºÀ°¦£!¯uªº«Ü¤£¦n·N«ä¡A¦]¬°³o­ÓÀɮ׸̦³«Ü¦h§OªºÀɮתº³sµ²¸ò¨ä¥L«e¤H¼gªº¥¨¶°(¬Ý¤£À´...)ÁÙ¦³¤u§@ªí¤]·|ÀHMNo¼W¥[¦Ó¼W¥[¡A§Ú¦³¸ÕµÛ§â¤@¨Ç¤u§@ªí²¾°£µ²ªG¥X²{¸ê®Æ¤F¡A«á¨Ó§Ú§â´X­Ó¶]¤£¥X¸ê®Æªº¯d¦í¤@¨Ç¼Æ¾Ú¸ê®Æ²¾°£«á¥»¥´ºâ­n±Hµ¹±z°Ñ¦Ò¡Aµ²ªG¦A¶]¤@¦¸©~µM¥X²{¸ê®Æ¤F...

ÀH«Hªþ¤W³o­Ó¥X²{"°õ¦æ¶¥¬q¿ù»~'13' : «¬ºA¤£²Å¦X" ªº¼Æ¾Úµ¹±z¨Ñ°Ñ¦Ò¡C
·P¿E¤£ºÉ!

¥®¸X¶é¤j¯Zªº
®z¤k¤l¯d

TOP

¦^´_ 8# sax868
³oºØ°ÝÃD±`µo¥Í¦b°}¦C¤¸¯Àªº¦r¤¸¼Æ¶W¹L256­Ó¦r¤¸©Ò²£¥Í
¾É­PµLªk¥¿½T¾Þ§@°}¦CÂà¸m
§ï¦¨¤@¤@µ¹­È´N¥i¥H
  1.      x = x + 1
  2.    End If
  3.    r = 12
  4.    Do Until .Cells(r, 1) = ""
  5.       ReDim Preserve Ar(57, x)
  6.          Ar(0, x) = .[C1].Value: Ar(1, x) = .[C2].Value: Ar(2, x) = .[E1].Value
  7.          s = 3
  8.          For Each a In .Range(.Cells(r, "A"), .Cells(r, "BB")).Value
  9.             Ar(s, x) = a
  10.             s = s + 1
  11.          Next
  12.          x = x + 1: r = r + 1
  13.    Loop
  14.   End If
  15. End With
  16. Next
  17. With Sheets.Add(after:=Sheets(Sheets.Count))
  18. For i = 0 To UBound(Ar, 2)
  19.    For j = 0 To 56
  20.    .[A1].Offset(i, j) = Ar(j, i)
  21.    Next
  22. Next
  23. End With
  24. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ sax868 ©ó 2012-5-8 12:10 ½s¿è

not ok.rar (55.18 KB) ¦^´_ 9# Hsieh

·PÁ¶W¯Åª©¥Dªº±Ï©R¤§®¦!! ¶W¯Å¦n¥Î!¤j·§µ¥2¤ÀÄÁµª®×¥þ¶]¥X¨Ó¡A³o¯uªº¤Ó¯«©_¤F!!
±µ¤U¨Óªº²Ä¤G­Ó°ÝÃDÁÙ±o¦A·Ð½Ð¶W¯Åª©¥DÀ°§Ú¸Ñ¨M...
§Ú¸ÕµÛ¼g¨ç¼ÆµM«á¥Î¿ý¥¨¶°ªº¤è¦¡¥i¬OµLªk¨C±i¤u§@ªí³£¤@°_§ï¡A¥H¤U¬O§Úªº»Ý¨D¡G
¨C¤@±i¤u§@ªí¸ÌFilter(Array("Currency", "DATA", "Updated Data")ªºAI12¦ÜAI300­È¬°:
­YC1='Updated Data'¸ÌªºA:AÄæ¥BA12='Updated Data'!¸ÌªºD: DÄæ¥BJ12='Updated Data'!¸ÌªºM:MÄæ¡A
«hAI12­È¬°'Updated Data'!AL:ALÄæ¸Ì¬Û¹ïÀ³ªº­È¡A¨Ã¥BA12¦ÜA300­È­Y¬°ªÅ®æ¡AAI12¦ÜAI300Ä欰ªÅ®æ¡A­Y¦³­È«h»Ý­pºâ¡F­YAI12¦ÜAI300Äæ¦ì­È¥X²{¹s«h·N±N¨ä­È³]¬°ªÅ®æ¡C

¨C¤@±i¤u§@ªí¸ÌFilter(Array("Currency", "DATA", "Updated Data")ªºAU12¦ÜAU300­È¬°:
­YC1='Updated Data'¸ÌªºA:AÄæ¥BA12='Updated Data'!¸ÌªºD: DÄæ¥BJ12='Updated Data'!¸ÌªºM:MÄæ¡A
«hAI12­È¬°'Updated Data'!AX:AXÄæ¸Ì¬Û¹ïÀ³ªº­È¡A¨Ã¥BA12¦ÜA300­È­Y¬°ªÅ®æ¡AAU12¦ÜAU300Ä欰ªÅ®æ¡A­Y¦³­È«h»Ý­pºâ¡F­YAU12¦ÜAU300Äæ¦ì­È¥X²{¹s«h·N±N¨ä­È³]¬°ªÅ®æ¡C

Á|¨Ò: ·Ð½Ð°Ñ¦Ò¤u§@ªí'1033600-2013' AI12Äæ¤ÎAU12Äæ§Úªº¨â¥ú¨ç¼Æ³o»ò¼g: =IF(A12="","",IF(AND(C1='Updated Data'!A2)+AND(A12='Updated Data'!D2)+AND(J12='Updated Data'!M2),'Updated Data'!AL2,""))   ¥i¬O³o©Û¦A¥t¤@±i¤u§@ªí'1006600-2013'´N¤£ÆF¥ú...:L

*­è­è§Ú¥ÎIndex+match¦n¹³¥i¥HÆF¥ú¤F!
¨Ò¤u§@ªí'1033600-2013' ¡G
AI12Äæ {=IF(A12="","",INDEX('Updated Data'!AL:AL,MATCH($C$1&$A12&$J12,'Updated Data'!A:A&'Updated Data'!D: D&'Updated Data'!M:M,0)))}
AU12Äæ {=IF(A25="","",INDEX('Updated Data'!AX:AX,MATCH($C$1&$A25&$J25,'Updated Data'!A:A&'Updated Data'!D: D&'Updated Data'!M:M,0)))}

¥i¬O¤£ª¾¬°Ô£¤@©w±o¦b³Ì«á«öCtrl+Shift+Enter¤~¯à¶]¥X¼Æ¾Ú,³o¨ç¼Æª½±µ½Æ»s¶KAI13:AI300®É¥þÅÜ'#VALUE!'...

¥H¤W¡A§Æ±æ¦³¸ÑÄÀ²M·¡...

«ô°U...½Ð¶W¯Åª©¥D©âªÅ¤Î¦U¦ì°ª¤â¤j¤j±Ï±Ï§Ú!!

¥®¥®¯Zªº®z¤k¤l¯d:$

TOP

        ÀR«ä¦Û¦b : ¦¨¥\¬OÀuÂIªºµo´§¡A¥¢±Ñ¬O¯ÊÂIªº²Ö¿n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD