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

[µo°Ý] ¦Û°Ê®Mªí

[µo°Ý] ¦Û°Ê®Mªí

Dear,
§Ú¤£ª¾¹D³o­Ó®Mªí°ÝÃD¯à§_¥Î¨ç¼Æ¸Ñ¨M,¦ý¦]¬°ªí®æ¤¤ªº¸ê®Æ¤j¦h¥Î¨ç¼Æ®M¥X¨Óªº,©Ò¥H¦b³o¸Ìµo°Ý...    ¦Û°Ê®Mªí.rar (34.84 KB)
        ¥X³fsheet¬O¤@­Ó¨C¤Ñ¥X³f¥Îªºªí®æ,©Ò¥H¸ê®Æ¬OÅܰʪº
        ¦]¬°Àɮ׫ܤj,§Ú§â»P¸É³f©ú²ÓµLÃöªº¸ê®Æ³£Delete,ÁקK¤zÂZ
        ¨C¤Ñªº¯Ê³f³£»Ý­n³o±i³øªí¥hµoµ¹¸É³fªº³æ¦ì
        °£¤FªíÀY1:2¥~,³o±i³øªí¨C¤Ñªì©l³£¬OªÅ¥Õªº
        §Ú­n§â¥X³fsheetªº¸ê®Æ®M¶i¨Ó,½Ð±Ð¥Î¨ç¼Æ¤ñ¸û¦n°µ,ÁÙ¬OVBA?
       
        ¥X³fsheet­n®M¹L¨Óªº³f®Æ:
1..        ¦³¯Ê³fªº³¡¥÷¡A¨Ì§Ç±qAS:BH±a¸ê®Æ
2..        §Ú¥ý°µ¤@­Ó2­Ó½d¨Ò"ªL¤f" & "·x·x1"
3..        ²{¦bµLªk¹F¦¨ªº³¡¥÷¦b©ó
        ­n¦p¦óÅý¦³¯Ê³fªºAS:BHªºªíÀY¦Û°Ê±a¨ì³o¸ÌªºBÄæ?¦ýµL¯Ê³fªº¦Û°Ê¸õ¹L¡I
        ¨Ã¥BÅý¯Ê³fªº®Æ¸¹¦Û°Ê±a¨ìAÄæ?
4..        ²Ä2µ§¯Ê³fªºªíÀY "·x·x1"±a¤JBÄæ,®Æ¸¹¦Û°Ê±a¨ìAÄæ«á
        ¦p¦óÅýEÄ檺¤½¦¡¥H·x·x1¬°¹ï¶H?¦Ó¤£¥Î¦U§O­×§ï¤½¦¡?
¨Ò¡G        =SUMPRODUCT((¥X³f!$F$4:$F$12=$A7)*(¥X³f!$AS$3:$BH$3=B$6)*(¥X³f!$AS$4:$BH$12))
        ¦p¦óÅý(¥X³f!$AS$3:$BH$3¦Û°Ê·j´MB$6),¦Ó¤£¥Î¦U§O§ï?

Dear,
§Ú¤£ª¾¹D³o­Ó®Mªí°ÝÃD¯à§_¥Î¨ç¼Æ¸Ñ¨M,¦ý¦]¬°ªí®æ¤¤ªº¸ê®Æ¤j¦h¥Î¨ç¼Æ®M¥X¨Óªº,©Ò¥H¦b³o¸Ìµo°Ý...   
...
PJChen µoªí©ó 2019-11-5 19:02


¨Ï¥ÎÀx¦s®æ¤½¦¡ªº¤è¦¡§Ú·Q¤£¥X¨Ó,
¦b¦¹¥u¯à¨Ï¥Î Excel VBA ¹Á¸Õ¹F¦¨ :
¥u­nÂIÀ» ¼t¯Êªí ªº "²£¥Í©ú²Ó" «ö¶s,
µ²ªG´N¥X¨Ó¤F...
µ{¦¡¦p¤U :
  1. Private Sub CbCreat_Click() ' ²£¥Í©ú²Ó
  2.   Dim iI%, iCol%
  3.   Dim lRow&
  4.   Dim sStr$
  5.   Dim diPro, diTit, vA, vD1, vD2
  6.   Dim wsSou As Worksheet, wsTar As Worksheet
  7.   
  8.   Set wsSou = Sheets("¥X³f")
  9.   Set wsTar = Sheets("¼t¯Êªí")
  10.   Set diPro = CreateObject("Scripting.Dictionary") ' °Ó«~¯Ê¼Æ
  11.   Set diTit = CreateObject("Scripting.Dictionary") ' ¾ÚÂI¦C¼Æ
  12.   
  13.   
  14.   With wsSou ' Ū¨ú¥X³f¸ê®Æ
  15.     iCol = 45 '¼t¯Ê°_©l¦æ
  16.     While .Cells(3, iCol) <> ""
  17.       sStr = .Cells(3, iCol) ' ¾ÚÂI¦W
  18.       lRow = 4 '°Ó«~°_©l¦C
  19.       While .Cells(lRow, 8) <> "" ' °Ó«~¦WºÙ
  20.         If .Cells(lRow, iCol) > 0 Then
  21.           If diPro.Exists(sStr) Then
  22.             diPro(sStr) = diPro(sStr) & "," & lRow & "-" & .Cells(lRow, iCol)
  23.           Else
  24.             diPro(sStr) = lRow & "-" & .Cells(lRow, iCol)
  25.           End If
  26.         End If
  27.         lRow = lRow + 1
  28.       Wend
  29.       iCol = iCol + 1
  30.     Wend
  31.   End With
  32.   
  33. '12 ²Ê 18
  34. '[d8].Font.ColorIndex = 23


  35.   With wsTar ' ²£¥Í¼t¯Êªí
  36.     .Range(.[A6], .Cells(Rows.Count, 8)).Delete Shift:=xlShiftUp
  37.     lRow = 6
  38.     For Each vA In diPro
  39.       With .Cells(lRow, 2).Resize(, 6) ' ¾ÚÂI¦W
  40.         With .Cells(1)
  41.           .Value = vA
  42.           diTit(vA) = lRow
  43.           .HorizontalAlignment = xlCenter
  44.           .VerticalAlignment = xlCenter
  45.           With .Font
  46.             .Size = 22
  47.             .Bold = False
  48.           End With
  49.         End With
  50.         
  51.         With .Interior
  52.             .Pattern = xlPatternLinearGradient
  53.             .Gradient.Degree = 90
  54.             .Gradient.ColorStops.Clear
  55.           With .Gradient.ColorStops.Add(0)
  56.             .ThemeColor = xlThemeColorDark1
  57.             .TintAndShade = 0
  58.           End With
  59.           With .Gradient.ColorStops.Add(1)
  60.             .Color = 118671
  61.             .TintAndShade = 0
  62.           End With
  63.         End With
  64.       End With
  65.       lRow = lRow + 1
  66.       
  67.       sStr = diPro(vA)
  68.       vD1 = Split(sStr, ",")
  69.       For iI = 0 To UBound(vD1)
  70.         vD2 = Split(vD1(iI), "-")
  71.         .Cells(lRow, 1) = wsSou.Cells(vD2(0), 6) ' ®Æ¸¹
  72.         .Cells(lRow, 2) = wsSou.Cells(vD2(0), 8) ' °Ó«~¦WºÙ
  73.         .Cells(lRow, 4) = wsSou.Cells(vD2(0), 7) ' ½c¤J¼Æ
  74.         .Cells(lRow, 5) = vD2(1) ' ¤í²~¼Æ
  75.         .Cells(lRow, 6) = Int(vD2(1) / .Cells(lRow, 4)) ' ½c
  76.         .Cells(lRow, 7) = vD2(1) Mod .Cells(lRow, 4) ' ²~
  77.         .Cells(lRow, 8) = wsSou.Cells(vD2(0), 5) ' ½¦±a
  78.         lRow = lRow + 1
  79.       Next
  80.     Next
  81.    
  82.     lRow = lRow - 1
  83.     With .Range(.[B6], .Cells(lRow, 7))
  84.       .Borders(xlEdgeLeft).LineStyle = xlContinuous
  85.       .Borders(xlEdgeTop).LineStyle = xlContinuous
  86.       .Borders(xlEdgeBottom).LineStyle = xlContinuous
  87.       .Borders(xlEdgeRight).LineStyle = xlContinuous
  88.       .Borders(xlInsideVertical).LineStyle = xlContinuous
  89.       .Borders(xlInsideHorizontal).LineStyle = xlContinuous
  90.     End With
  91.     For Each vA In diTit
  92.       With .Cells(diTit(vA), 2).Resize(, 6)
  93.         .Borders(xlInsideVertical).LineStyle = xlNone
  94.         .Borders(xlInsideHorizontal).LineStyle = xlNone
  95.       End With
  96.     Next
  97.   End With
  98.   MsgBox "¯Ê®Æ©ú²Ó¤w²£¥Í§¹²¦..."
  99. End Sub
½Æ»s¥N½X
¦Û°Ê®Mªí-Ans.zip (51.69 KB)

TOP

¦^´_ 2# luhpro
¯u¬O¤Ó·PÁ¤F...

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2019-11-16 23:17 ½s¿è

¦^´_ 2# luhpro

½Ð°Ý¤j¤j,
¤@¯ë§Ú¦b¥ÎVBA®É,¥ÎAlt + F11´N¥i¥H¬Ý¨ìModuleªºµ{¦¡½X,¬°¤°»ò±z¼gªºµ{¦¡¬Ý¤£¨ìModule ?
¥t¥~¦]¬°ªí®æ¤@¶}©lªºª¬ºA³£¬OªÅ¥Õªº,¶ñ¤J¸ê®Æ®É­n±q²Ä3¦C¶}©l,¦ý¦b´ú¸Õ®É³£·|±q²Ä6¦C¶}©l¶ñ,¥i§_À°¦£§ï¬°°_©l¥Ñ²Ä3¦C¶}©l¼g¤J¸ê®Æ?
¥t¥~§Úªºµ{¦¡­n©ñ¦b¥t¤@ÀÉ®×Macro.xlsm¤¤°õ¦æ,¨Ã«ü©wÀɦW"³Ì·s®w¦s.xlsx"½Ð°Ýµ{¦¡­n«ç»ò­×¥¿?
ÁÂÁ±z

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2019-11-17 01:17 ½s¿è
¦^´_  luhpro

½Ð°Ý¤j¤j,
¤@¯ë§Ú¦b¥ÎVBA®É,¥ÎAlt + F11´N¥i¥H¬Ý¨ìModuleªºµ{¦¡½X,¬°¤°»ò±z¼gªºµ{¦¡¬Ý¤£¨ìModule ?

PJChen µoªí©ó 2019-11-16 23:12

¨º¥u¬O§Ú­Ìªº¤Á´«¤è¦¡¤£¦P,
§A¥u­nÂI¤@¤U¦p¤U¹Ïªº "À˵øµ{¦¡½X" «ö¶s´N¥i¥H¬Ý¨ì¤F.
À˵øµ{¦¡½X«ö¶s.jpg

¥t¥~¦]¬°ªí®æ¤@¶}©lªºª¬ºA³£¬OªÅ¥Õªº,¶ñ¤J¸ê®Æ®É­n±q²Ä3¦C¶}©l,¦ý¦b´ú¸Õ®É³£·|±q²Ä6¦C¶}©l¶ñ,¥i§_À°¦£§ï¬°°_©l¥Ñ²Ä3¦C¶}©l¼g¤J¸ê®Æ?

¦]¬°§Aªº½d¨Ò¾×¬Ý¨ìªº¬O±q²Ä6¦C¶}©lªº.(ÁôÂäF²Ä3-5¦C)
»Ý­n­×§ï©³¤U³o¦æ¼Æ¦r :
    With wsTar ' ²£¥Í¼t¯Êªí
    .Range(.[A6], .Cells(Rows.Count, 8)).Delete Shift:=xlShiftUp

    lRow = 3
    For Each vA In diPro
ÁÙ¦³©³¤U³o¦æ :
    lRow = lRow - 1
    With .Range(.[B3, .Cells(lRow, 7))
       .Borders(xlEdgeLeft).LineStyle = xlContinuous

¥t¥~§Úªºµ{¦¡­n©ñ¦b¥t¤@ÀÉ®×Macro.xlsm¤¤°õ¦æ,¨Ã«ü©wÀɦW"³Ì·s®w¦s.xlsx"½Ð°Ýµ{¦¡­n«ç»ò­×¥¿?

  Dim sStr$, sPath$, sFlName$
...
  Set wsSou = ThisWorkbook.Sheets("¥X³f")
  Set wsTar = Sheets("¼t¯Êªí")  <=§R±¼³o¦æ
  Set diPro = CreateObject("Scripting.Dictionary") ' °Ó«~¯Ê¼Æ  
   sPath = ThisWorkbook.Path ' ¦pªG­n«ü©w¥Ø¿ý, ¥u­n§ï¦¨¸Ó¥Ø¿ý§Y¥i, ¦p sPath = "D:"
  sFlName = "³Ì·s®w¦s.xlsx"
  bMatch = False ' Àˬd '³Ì·s®w¦s.xlsx' Àɮ׬O§_¤w¶}±Ò
  For iI = 1 To Workbooks.Count
    If Workbooks(iI).Name = sFlName Then
      bMatch = True
      Exit For
    End If
  Next iI
  If bMatch Then
    Set wsTar = Workbooks(sFlName).Sheets("¼t¯Êªí")
    wsTar.Activate
  Else
    Set wsTar = Workbooks.Open(Filename:=sPath & "\" & sFlName).Sheets("¼t¯Êªí")
  End If

³Ì«á, ­ì¥ýªºÀɮפ¤,
¦r§Î¤j¤p»P½c¤J¼ÆÃC¦â§Ñ¤F§ï,
´N¤@¨Ö³B²z¤F :
     With .Range(.[B3], .Cells(lRow, 7))
      .Font.Size = 18
   ...
    End With
    For Each vA In diTit
...
    Next

    With .Range(.[D3], .Cells(lRow, 3))
      .Font.ColorIndex = 23
    End With


­×§ï«á§¹¾ãµ{¦¡¦p¤U :
Private Sub CbCreat_Click() ' ²£¥Í©ú²Ó
  Dim iI%, iCol%
  Dim lRow&
  Dim sStr$, sPath$, sFlName$
  Dim bMatch As Boolean
  Dim diPro, diTit, vA, vD1, vD2
  Dim wsSou As Worksheet, wsTar As Worksheet
  
  Set wsSou = ThisWorkbook.Sheets("¥X³f")
  Set diPro = CreateObject("Scripting.Dictionary") ' °Ó«~¯Ê¼Æ
  Set diTit = CreateObject("Scripting.Dictionary") ' ¾ÚÂI¦C¼Æ
  
  sPath = ThisWorkbook.Path ' ¦pªG­n«ü©w¥Ø¿ý, ¥u­n§ï¦¨¸Ó¥Ø¿ý§Y¥i, ¦p sPath = "D:"
  sFlName = "³Ì·s®w¦s.xlsx"
  bMatch = False ' Àˬd '³Ì·s®w¦s.xlsx' Àɮ׬O§_¤w¶}±Ò
  For iI = 1 To Workbooks.Count
    If Workbooks(iI).Name = sFlName Then
      bMatch = True
      Exit For
    End If
  Next iI
  If bMatch Then
    Set wsTar = Workbooks(sFlName).Sheets("¼t¯Êªí")
    wsTar.Activate
  Else
    Set wsTar = Workbooks.Open(Filename:=sPath & "\" & sFlName).Sheets("¼t¯Êªí")
  End If
  
  With wsSou ' Ū¨ú¥X³f¸ê®Æ
    iCol = 45 '¼t¯Ê°_©l¦æ
    While .Cells(3, iCol) <> ""
      sStr = .Cells(3, iCol) ' ¾ÚÂI¦W
      lRow = 4 '°Ó«~°_©l¦C
      While .Cells(lRow, 8) <> "" ' °Ó«~¦WºÙ
        If .Cells(lRow, iCol) > 0 Then
          If diPro.Exists(sStr) Then
            diPro(sStr) = diPro(sStr) & "," & lRow & "-" & .Cells(lRow, iCol)
          Else
            diPro(sStr) = lRow & "-" & .Cells(lRow, iCol)
          End If
        End If
        lRow = lRow + 1
      Wend
      iCol = iCol + 1
    Wend
  End With

  With wsTar ' ²£¥Í¼t¯Êªí
    .Range(.[A6], .Cells(Rows.Count, 8)).Delete Shift:=xlShiftUp
    lRow = 3
    For Each vA In diPro
      With .Cells(lRow, 2).Resize(, 6) ' ¾ÚÂI¦W
        With .Cells(1)
          .Value = vA
          diTit(vA) = lRow
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlCenter
          With .Font
            .Size = 22
            .Bold = False
          End With
        End With
        
        With .Interior
            .Pattern = xlPatternLinearGradient
            .Gradient.Degree = 90
            .Gradient.ColorStops.Clear
          With .Gradient.ColorStops.Add(0)
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
          End With
          With .Gradient.ColorStops.Add(1)
            .Color = 118671
            .TintAndShade = 0
          End With
        End With
      End With
      lRow = lRow + 1
      
      sStr = diPro(vA)
      vD1 = Split(sStr, ",")
      For iI = 0 To UBound(vD1)
        vD2 = Split(vD1(iI), "-")
        .Cells(lRow, 1) = wsSou.Cells(vD2(0), 6) ' ®Æ¸¹
        .Cells(lRow, 2) = wsSou.Cells(vD2(0), 8) ' °Ó«~¦WºÙ
        .Cells(lRow, 4) = wsSou.Cells(vD2(0), 7) ' ½c¤J¼Æ
        .Cells(lRow, 5) = vD2(1) ' ¤í²~¼Æ
        .Cells(lRow, 6) = Int(vD2(1) / .Cells(lRow, 4)) ' ½c
        .Cells(lRow, 7) = vD2(1) Mod .Cells(lRow, 4) ' ²~
        .Cells(lRow, 8) = wsSou.Cells(vD2(0), 5) ' ½¦±a
        lRow = lRow + 1
      Next
    Next
   
    lRow = lRow - 1
    With .Range(.[B3], .Cells(lRow, 7))
      .Font.Size = 18
      .Borders(xlEdgeLeft).LineStyle = xlContinuous
      .Borders(xlEdgeTop).LineStyle = xlContinuous
      .Borders(xlEdgeBottom).LineStyle = xlContinuous
      .Borders(xlEdgeRight).LineStyle = xlContinuous
      .Borders(xlInsideVertical).LineStyle = xlContinuous
      .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    End With
    For Each vA In diTit
      With .Cells(diTit(vA), 2).Resize(, 6)
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
      End With
    Next
    With .Range(.[D3], .Cells(lRow, 3))
      .Font.ColorIndex = 23
    End With
  End With
  MsgBox "¯Ê®Æ©ú²Ó¤w²£¥Í§¹²¦..."
End Sub

¦Û°Ê®Mªí-Ans2.zip (56.31 KB)

TOP

¦^´_ 5# luhpro

·PÁÂ ¤j¤j,
°õ¦æ¨S¦³°ÝÃD¤F

TOP

¦^´_ 5# luhpro

SOS
¤j¤j,
§Ú¨Ì¹ê»Ú»Ý¨D,¤p¤pªº§ï¤Fµ{¦¡,´ú¸Õ®É¤]³£¨S°ÝÃD,¦ý...
µ{¦¡¦b¥¿¦¡¨Ï¥Î®É,µo¥Í¤F°ÝÃD
°±¦b³o¸Ì ".Cells(lRow, 6) = Int(vD2(1) / .Cells(lRow, 4)) ' ½c"
¦Ó¥B¶]¥X¤@¨Ç´ú¸Õ®É¥¼´¿¥X²{ªº¸ê®Æ,
§Ú§â¥¦¯d¦b°õ¦æµ²ªG¤¤,
½ÐÀ°¦£¬Ý¤U....·PÁÂ
3rd_µ{¦¡»P°õ¦æÀÉ ¤À¶}.rar (136.08 KB)

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2019-11-20 00:01 ½s¿è
¦^´_  luhpro

SOS
¤j¤j,
§Ú¨Ì¹ê»Ú»Ý¨D,¤p¤pªº§ï¤Fµ{¦¡,´ú¸Õ®É¤]³£¨S°ÝÃD,¦ý...
µ{¦¡¦b¥¿¦¡¨Ï¥Î®É,µo ...
PJChen µoªí©ó 2019-11-19 22:01

­º¥ý­×¥¿¤@­Ó¿ù»~, ¬õ¦â³o¤@¦æ­n²¾¨ì³o¸Ì :
  End If
    Set wsSou = Workbooks(sFlName).Sheets("­¸¤ñ")
  With wsSou ' Ū¨ú­¸¤ñ¸ê®Æ
§Ú­Ì¤£¯à¦b "³Ì·s®w¦s.xlsx" ³o­ÓÀÉ®×ÁÙ¨S¥´¶}«e´N¥h°Ñ·Ó¥¦.

¨ä¦¸¦^µª§Aªº°ÝÃD,
¨º¦æ¦³°ÝÃD¬O¦]¬° vD2(1) ¬O ªÅ¦r¦ê (§Y "" ),
¨t²Î¤£±µ¨ü±N¥¦¥Î¨Ó°Ñ»P­pºâ¦¡.
¦A¦Ò¼{¨ì§Aªº¤í²~¼Æ·|¥X²{¤é´Á,
©Ò¥H§Ú±q·½ÀYª½±µÂo±¼¤£²Å¦X¼Æ¦r®æ¦¡ªº¸ê®Æ :
  Dim rTar As Range
...
      sStr = .Cells(3, iCol) ' ¾ÚÂI¦W
      lRow = 4 '°Ó«~°_©l¦C
      While .Cells(lRow, 8) <> "" ' °Ó«~¦WºÙ
        Set rTar = .Cells(lRow, iCol)
        If rTar > 0 And rTar <> "" And InStr(1, rTar, "/") = 0 Then
          If diPro.Exists(sStr) Then
            diPro(sStr) = diPro(sStr) & "," & lRow & "-" & rTar
...
        vD2 = Split(vD1(iI), "-")
        sStr = vD2(1)
...
        .Cells(lRow, 5) = sStr ' ¤í²~¼Æ
        '.Cells(lRow, 6) = Int(vD2(1) / .Cells(lRow, 4)) ' ½c <= §R°£³o¦æ
        If InStr(1, sStr, "½c") > 0 Then ' ³B²z¦³ "½c+" ªº¦r¦ê
          .Cells(lRow, 6) = Left(sStr, InStr(1, sStr, "½c") - 1) ' ½c
          .Cells(lRow, 7) = Val(Mid(sStr, InStr(1, sStr, "½c") + 1)) ' ²~
        Else ' ¥¿±`ªº²~¼Æ
          .Cells(lRow, 6) = Int(sStr / .Cells(lRow, 4)) ' ½c
          .Cells(lRow, 7) = sStr Mod .Cells(lRow, 4) ' ²~
        End If

³Ì«á, ¦]¬°¦³ "½c+" ªº¦r¦ê¤Óªø¤£¯à¥þ³¡Åã¥Ü¥X¨Ó,
¬G½Õ¾ã E ÄæÄæ¼e :
    .Columns("E").AutoFit
  End With
  MsgBox "¯Ê®Æ©ú²Ó¤w²£¥Í§¹²¦..."
Macro-2.zip (21.63 KB)

TOP

¦^´_ 8# luhpro

¤j¤j,
´ú¸Õ«á."¦ü¥G"¬Ý¤£¥X¦³§ïÅÜ吔...
¦³¯Ê³fªº³¡¥÷¡A¨Ì§Ç±q"­¸¤ñ"AS:BH±a¸ê®Æ,´ú¸ÕÀɤ¤ªº¯Ê³f¨ä¹ê¥u¦³¤@¶µ(72²~)
¥i¬O»sªí§¹¦¨«á·|§â­q³æªº¼Æ¶q±a¨ì¼t¯Ê!!
¥t¥~¤]·Q½Ð°Ý,¤é«á­Y¥X³f¾ÚÂI¼W¥[®É,"­¸¤ñ"AS:BHÄæ¼Æ¤]·|¼W¥[,½Ð°Ý·í¾ÚÂI¼W¥[®É­n¦p¦ó­×§ï?

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2019-11-20 22:31 ½s¿è
¦^´_  luhpro

¤j¤j,
¦³¯Ê³fªº³¡¥÷¡A¨Ì§Ç±q"­¸¤ñ"AS:BH±a¸ê®Æ,
´ú¸ÕÀɤ¤ªº¯Ê³f¨ä¹ê¥u¦³¤@¶µ(72²~), ...
PJChen µoªí©ó 2019-11-20 21:03


¶â? ÁÙ¬O¸ò¤§«e¤@¼Ë·|µo¥Í¿ù»~¶Ü?
8# ¥D­n¬O¸Ñ¨M ""(ªÅ¦r¦ê) »P "½c+" ³y¦¨­pºâ¿ù»~ªº°ÝÃD,
¤£¹L¨Ì§A©Ò­z¨º¨ä¹ê¬O§A¨S­n§ì¨úªº³¡¤À.
©Ò¥H§AÅܦ¨¥u­n§â 7# ªºµ{¦¡­×§ï³o¤@¦æÀ³¸Ó´N¬O§A­nªºµ²ªG¤F :
    iCol = 45 '¼t¯Ê¨Ó·½°_©lÄæ
    While .Cells(3, iCol) <> "¹º³æ¦X­p"
      sStr = .Cells(3, iCol) ' ¾ÚÂI¦W

¤£¹L³o­Ó¤´µM­n§ï :
sFlName = "³Ì·s®w¦s.xlsx"
  Set wsSou = Workbooks(sFlName).Sheets("­¸¤ñ")
  Set diPro = CreateObject("Scripting.Dictionary") ' °Ó«~¯Ê¼Æ
¬õ¦â³o¤@¦æ­n²¾¨ì³o¸Ì :
End If
    Set wsSou = Workbooks(sFlName).Sheets("­¸¤ñ")
   With wsSou ' Ū¨ú­¸¤ñ¸ê®Æ

¥t¥~, ³o¦æ¤]­n­×§ï :
  With wsTar ' ²£¥Í¼t¯Êªí
    .Range(.[A3], .Cells(Rows.Count, 8)).Delete Shift:=xlShiftUp
    lRow = 3

¥t¥~¤]·Q½Ð°Ý,¤é«á­Y¥X³f¾ÚÂI¼W¥[®É,"­¸¤ñ"AS:BHÄæ¼Æ¤]·|¼W¥[,½Ð°Ý·í¾ÚÂI¼W¥[®É­n¦p¦ó­×§ï?

­n¥[¤W¾ÚÂI,
¥u­n¦b "¹º³æ¦X­p" (§Y BI Äæ)¥ª¤è´¡¤J¾ãÄæ¨Ã½á»P¸ê®Æ§Y¥i.

TOP

        ÀR«ä¦Û¦b : ¦³¦h¤Ö¤O¶q´N°µ¦h¤Ö¨Æ¡A¤£­n¤ß¦sµ¥«Ý¡Aµ¥«Ý¤~·|¸¨ªÅ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD