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

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

#VALUE!.zip (134.69 KB) ¦^´_ 9# Hsieh
¿Ë·Rªº¶W¯Åª©¥D¤Î¦U¦ì°ª¤â¤j¤j­Ì¤È¦w¡G

§Ú­è¥Î§Ú¨â¥ú¤T¸}¿ßªº¾Ç²ß¿ý¥¨¶°¡A§â§Ú·Q­nªº¤è¦¡ªí¹F¥X¨Ó¤F¡I(½Ð¸Ô¨£ªþÀÉ)
¸Õ·Q·Ð½Ð¤j¤j­ÌÀ°§Ú¬Ý¤@¤U¯à¤£¯àÁYµuVBA¥H´£°ªexcel ¹Bºâªº³t«×©O?
¥t¥~¡A¦]¬°§Ú¥u·|¿ý³æ¤@±i¤u§@ªí¡A½Ð°Ý¥i¤£¥i¥H³]©w­n¤@¦¸¶]©Ò¦³Àɮ׸̪º¤u§@ªí,°£¤F'Currency'¡B'DATA'¤Î'Updated Data'¤T±i¤u§@ªí¥H¥~©O?
*§Ú¬Ý¶W¯Åª©¥DHsieh¤j¼gªº³o­Ó·Q»¡´¡¤J§Y¥i¡A¥i¬O§Ú¤£·|¥Î...-_-lll
With Sh
  If UBound(Filter(Array("Currency", "DATA", "Updated Data"), .Name, True)) < 0 Then
  ReDim Preserve Ar(57, x)

¯u¤£¦n·N«ä¡A³Â·Ð¤j®a¤F!

§V¤O´å¥X¥®¥®¯Zªº
®z¤k¤l¯d

TOP

¦^´_ 11# sax868
¬O­n§â"Updated Data"¹ïÀ³ªº­È¼g¤J¨C­Ó¤u§@ªíªº12¦C¥H¤U¤§AUÄæ¶Ü?
  1. Sub Ex()
  2. Dim Sh As Worksheet, Ar()
  3. Set d = CreateObject("Scripting.Dictionary") '³Ð«Ø¦r¨åª«¥óÀx¦s"Updated Data"¹ïÀ³ªº­È
  4. For Each Sh In Sheets
  5. With Sheets("Updated Data")
  6.    For Each a In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
  7.      d(a & a.Offset(, 3) & a.Offset(, 12)) = a.Offset(, 49).Value '¥HA¡BD¡BM¬°¯Á¤Þ¦s¤JAXÄæ¦ìªº­È
  8.     Next
  9. End With
  10. With Sh
  11.   If UBound(Filter(Array("Currency", "DATA", "Updated Data"), .Name, True)) < 0 Then '°£¤F³o¨Ç¤u§@ªí¥H¥~°õ¦æ
  12.   ReDim Preserve Ar(57, x) 'ÂX¼W°}¦C
  13.   If IsEmpty(Ar(0, 0)) Then '¡A¦pªG°}¦CÁÙ¨S«Ø¥ß¥ý¼g¤J¼ÐÃD¦C
  14.      Ar(0, x) = .[B1].Value: Ar(1, x) = .[B2].Value: Ar(2, x) = .[D1].Value
  15.      s = 3
  16.      For Each a In .[A11:BB11].Value
  17.         Ar(s, x) = a
  18.         s = s + 1
  19.      Next
  20.      x = x + 1
  21.    End If
  22.    r = 12 '±q²Ä12¦C¥H¤U¶}©lŪ¤J¸ê®Æ¨ì°}¦C¤¤
  23.    Do Until .Cells(r, 1) = "" 'ª½¨ìAÄ欰ªÅ¥Õ¬°¤î
  24.       ReDim Preserve Ar(57, x)
  25.          Ar(0, x) = .[C1].Value: Ar(1, x) = .[C2].Value: Ar(2, x) = .[E1].Value
  26.          s = 3
  27.          For Each a In .Range(.Cells(r, "A"), .Cells(r, "BB")).Value '±NA:BBÄæ¦ìŪ¤J°}¦C
  28.             Ar(s, x) = a
  29.             s = s + 1
  30.          Next
  31.          .Cells(r, "AU") = d(Ar(0, x) & Ar(3, x) & Ar(12, x)) '±N¤u§@ªíªºAUÄæ¦ì¼g¤J¹ïÀ³ªºUpdated Data­È
  32.          x = x + 1: r = r + 1 '¤U¤@¦C
  33.    Loop
  34.   
  35.   End If
  36. End With
  37. Next
  38. With Sheets.Add(after:=Sheets(Sheets.Count)) '·s¼W¤u§@ªí©ó³Ì«á
  39. For i = 0 To UBound(Ar, 2)
  40.    For j = 0 To 56
  41.    .[A1].Offset(i, j) = Ar(j, i) '¤@¤@±N°}¦C¤¸¯À¼g¤JÀx¦s®æ
  42.    Next
  43. Next
  44. End With
  45. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 12# Hsieh

·PÁ¶W¯Åª©¥DªºÀ°¦£! «Ü©êºp¡A§Ú³o²V¶Ã¦aªí¹F¨SÅý±z¥ß§Y²M·¡©úÁA-_-lll
'Updated Data'¸ÌªºALÄ涷¹ïÀ³¨ì¨C¤@±i¤u§@ªíªº12¦C¥H¤U¤§AIÄæ¡A'Updated Data'¸ÌªºAXÄ涷¹ïÀ³¨ì¨C¤@±i¤u§@ªíªº12¦C¥H¤U¤§AUÄæ¡F¥ý«e°Ê§@¤@ªºVBA»P²{¦b­n§ìUpdated Data¸ÌªºAL/AX­È¦^¨C­Ó¤u§@ªíªºAI/AU¬°¤G­Ó¿W¥ß¤À¶}ªºVBA³á! (¦]¬°¬ðµMµo²{¥¦¶]¤F¤@¦¸¸ê®Æ)
¯u¤£¦n·N«ä¡A­n¦A³Â·Ð±z¤F!

¥®¥®¯Zªí¹F¯à¤O¤£¨Îªº
®z¤k¤l¯d

TOP

¦^´_ 13# sax868
  1. Sub InputData()
  2. Dim Sh As Worksheet, Ar()
  3. Set d = CreateObject("Scripting.Dictionary") '³Ð«Ø¦r¨åª«¥óÀx¦s"Updated Data"¹ïÀ³ªº­È
  4. Set d1 = CreateObject("Scripting.Dictionary") '³Ð«Ø¦r¨åª«¥óÀx¦s"Updated Data"¹ïÀ³ªº­È

  5. With Sheets("Updated Data")
  6.    For Each a In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
  7.      d(a & a.Offset(, 3) & a.Offset(, 12)) = a.Offset(, 49).Value '¥HA¡BD¡BM¬°¯Á¤Þ¦s¤JAXÄæ¦ìªº­È
  8.      d1(a & a.Offset(, 3) & a.Offset(, 12)) = a.Offset(, 37).Value '¥HA¡BD¡BM¬°¯Á¤Þ¦s¤JALÄæ¦ìªº­È
  9.    Next
  10. End With
  11. For Each Sh In Sheets
  12. With Sh
  13.    r = 12 '±q²Ä12¦C¥H¤U¶}©lŪ¤J¸ê®Æ¨ì°}¦C¤¤
  14.    Do Until .Cells(r, 1) = "" 'ª½¨ìAÄ欰ªÅ¥Õ¬°¤î
  15.          .Cells(r, "AU") = d(.[C1] & .Cells(r, "A") & .Cells(r, "J")) '±N¤u§@ªíªºAUÄæ¦ì¼g¤J¹ïÀ³ªºUpdated Data­È
  16.          .Cells(r, "AI") = d1(.[C1] & .Cells(r, "A") & .Cells(r, "J")) '±N¤u§@ªíªºAIÄæ¦ì¼g¤J¹ïÀ³ªºUpdated Data­È
  17.           r = r + 1 '¤U¤@¦C
  18.    Loop
  19. End With
  20. Next
  21. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 14# Hsieh
§Ú·P°Ê¦a¤j­ú¤@³õ...
·PÁ¶W¯Åª©¥Dªº±Ï©R¤§®¦...Á`ºâ§Ú¥i¥H±q¾m­I¤S¦ÇÀY¤gÁy¨ì©ú¤Ñ®¼ª½¸y±ì¬¡µÛ¨«¶i¿ì¤½«Ç¤F!!
»¡¤£ºÉªº·P¿E!!!

TOP

#VALUE!.zip (131.44 KB) ¦^´_ 14# Hsieh

¿Ë·Rªº¶W¯Åª©¥D¤È¦w¡G

¯u¤£¦n·N«ä! ½Ð°Ý¦bInput Data³o­ÓVBA¤¤¯à¤£¯à¦b§ì²Å¦X³o¤T¼Æ­Èªº¦P®É¡A¨Ã»Ý'Updated Data'ALÄæ©ÎAXÄ榳­Èªº¤~§ìALÄæ©ÎAXÄ檺­È?
With Sheets("Updated Data")
    For Each a In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
     d(a & a.Offset(, 3) & a.Offset(, 12)) = a.Offset(, 49).Value
     d1(a & a.Offset(, 3) & a.Offset(, 12)) = a.Offset(, 37).Value

¥Ø«e§Ú¹J¨ìªºª¬ªp¬O²Å¦X³o¤T¼Æ­Èªº¥i¯à¦³¦n´X¦C¡A¦ý¥u¦³¤@¦C¬O¦³Notes,
           Á|¨Ò(½Ð¬dªþ¥ó)¡G¤u§@ªí¡¥1008600-2013¡¦ ¦]¡¦Updated Data¡¨¸Ì²Å¦X³o¯S©w¤T¼Æ­Èªº(1008600+500+AA) ¦³¤T¦æ¦ý¶È¨ä¤¤¤@¦æ¦³Notes, ©Ò¥HVBA¶]¥X¨Óµª®×¬OªÅ¥Õ

²{¦b§Ú¯à·Q¨ìªº¬O¥ý§â³o¨ÇªÅ¥Õªº¤â°Ê§R°£«á¦A¶]InputData VBA§Y¥i¨D¥Xµª®×¡A°ÝÃD¬O³o¤@§R¦³¨Ç¸ê®Æ¦b¥þ³¡§ó·s«á´N§ä¤£¦^¨Ó¤F¡K·Q½Ð°Ý¯à¤£¯à³Â·Ð¶W¯Åª©¥DÀ°¦£¦h³]¤@­Ó±ø¥ó¡A¨Ï:
d(a & a.Offset(, 3) & a.Offset(, 12)) = a.Offset(, 49).Value ²Å¦X¥~¥BAXÄæ«DªÅ®æ
d1(a & a.Offset(, 3) & a.Offset(, 12)) = a.Offset(, 37).Value²Å¦X¥~¥BALÄæ«DªÅ®æ

·P¿E¤£ºÉ!!

¨S©Û¤Fªº
®z¤k¤l¯d:L

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-5-10 19:50 ½s¿è

¦^´_ 16# sax868
  1. Sub InputData()

  2. Dim Sh As Worksheet, Ar()

  3. Set d = CreateObject("Scripting.Dictionary")

  4. Set d1 = CreateObject("Scripting.Dictionary")


  5. With Sheets("Updated Data")

  6.    For Each a In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))

  7.     If a.Offset(, 49) <> "" Then d(a & a.Offset(, 3) & a.Offset(, 12)) = a.Offset(, 49).Value   'AX¦³­È¤~°õ¦æ

  8.     If a.Offset(, 37) <> "" Then d1(a & a.Offset(, 3) & a.Offset(, 12)) = a.Offset(, 37).Value   'AL¦³­È¤~°õ¦æ

  9.    Next

  10. End With

  11. For Each Sh In Sheets

  12. With Sh

  13.    r = 12

  14.    Do Until .Cells(r, 1) = ""

  15.          .Cells(r, "AU") = d(.[C1] & .Cells(r, "A") & .Cells(r, "J"))

  16.          .Cells(r, "AI") = d1(.[C1] & .Cells(r, "A") & .Cells(r, "J"))

  17.           r = r + 1

  18.    Loop

  19. End With

  20. Next

  21. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 17# Hsieh
¤Ó·P¿E±z¤F!!¥nÁ¶W¯Åª©¥DªºÀ°¦£!!


   
º¡¤ßÅw³ßªº
®z¤k¤l¯d

TOP

_VALUE!2.rar (119.74 KB) ¦^´_ 17# Hsieh

¿Ë·Rªº¶W¯Åª©¥D±ß¦w¡G

¦]¬°³o­ÓÀɮ׳sµ²³\¦hÀɮסA¾á¤ß·|¦³¨ä¥L¨Ï¥ÎªÌ²¨©¿¨S¦sÀÉ¥H­P¿ò¥¢¸ê®Æ¡C§Ú·Q¤F¤@¨Ç¤èªk¥Î¤T¸}¿ß¥\¤Ò¼g¤F¤@®M§âÀɮצ۰ÊÀx¦sªºVBA(¸m©óModel3¸Ì)¡A¦ý¬O¤£ÆF¥ú¡AÁÙ±o³Â·Ð½Ð¶W¯Åª©¥DÀ°¦£­×§ï¾É¥¿¡G

AutoSaveNotes:½Ð°Ý¦p¦ó¥ÎVBA³]©w¦b¨C15¤ÀÄÁ¤ÎÃöÀÉ®×®É(³o­Ó§Ú¤£·|)·|¦Û°Ê¸õ¥Xµøµ¡­n¨D¦sÀɤ@¦¸ («ö¡¨¬O¡¨«á¡A¶]¸Ó¥ý«e³]¸mªº°Ê§@¤@¡A°£¤F­n¦s¦b¤u§@ªí¡¦Updated Data¡¦¸Ì(¥ý«e¬O¥t¶}±Ò¤@­ÓÀÉ®×¥¹µoı§Ç¸¹·|¸õ±N¨Ó¨S¿ìªk§ì¦^¥h¡A©Ò¥Hª½±µ¦s¦bUpdated Data¤u§@ªí¸Ì§Y¥i)¡A¨Ã¦Û°Ê¥t¦sÀÉ©ó¸ô®|C:\Users\JH\Downloads\¡A¨äÀɦW¬°¡GNotes¡^¡A¸Ó¦s¬°³æ¤@¤u§@ªí¤§¦WºÙ¥ç¬°'Updated Data'¡C

¥H¤W¡A«ô°U¶W¯Åª©¥D±Ï±Ï§Ú!!

ÁÂÁÂ~

²´·úªáªáªº
®z¤k¤l¯d

TOP

¦^´_ 19# sax868
¦Ü©ó§A¾ãÅé¬yµ{§Ú¨Ã¤£²M·¡
¦Û°Ê¦sÀÉ¥i°Ñ¦Ò¤U­±³sµ²
¦Û­qApplicatoin¨Æ¥ó/ºÊ±±©Ò¦³¬¡­¶Ã¯/°µ¦¨¼W¯q¶°
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD