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

[µo°Ý] ¥i§_À°¦£§ïµ½°j°é³t«×

[µo°Ý] ¥i§_À°¦£§ïµ½°j°é³t«×

µ{¦¡½X¤U¡AÀɮפ¤½ÐÂIÀ»"¥t¦si-Link¸ô®|ÀÉ"°õ¦æ¥¨¶°¡AÁÂÁÂÀ°¦£~
  1. Sub ¶×¥X»sµ{¦Ü¸ô®|ÀÉ()

  2.     Application.ScreenUpdating = False
  3.     Application.DisplayAlerts = False
  4.     If Cells(11, "A") Like "¥~Æ[" Then       '¥~Æ[
  5.                 Cells(11, "F").Value = "D,1,1"
  6.                 Cells(11, "G").Value = "D,1,2"
  7.                 Cells(11, "H").Value = "D,1,3"
  8.                 Cells(11, "I").Value = "D,1,4"
  9.                 Cells(11, "J").Value = "D,1,5"
  10.     End If
  11.    
  12.     lastRow = Sheets("»sµ{Àˬd°O¿ýªí").Columns(2).Find(What:="", LookIn:=xlValues, _
  13.     SearchDirection:=xlNext, AFTER:=Range("B12")).Row
  14.    
  15.     For I = 12 To lastRow              'Range("A12").End(xlDown).Row
  16.             If Cells(I, "B") <> "" Then
  17.                 Cells(I, "F").Value = "D," & Cells(I, "A").Value + 1 & ",1"
  18.                 Cells(I, "G").Value = "D," & Cells(I, "A").Value + 1 & ",2"
  19.                 Cells(I, "H").Value = "D," & Cells(I, "A").Value + 1 & ",3"
  20.                 Cells(I, "I").Value = "D," & Cells(I, "A").Value + 1 & ",4"
  21.                 Cells(I, "J").Value = "D," & Cells(I, "A").Value + 1 & ",5"
  22.             Else
  23.             End If
  24.     Next
  25.    
  26.     For I = 47 To Range("A47").End(xlDown).Row
  27.         If Cells(I, "B") <> "" Then
  28.             If Cells(I, "B") <> "" Then
  29.                 Cells(I, "F").Value = "D," & Cells(I, "A").Value + 1 & ",1"
  30.                 Cells(I, "G").Value = "D," & Cells(I, "A").Value + 1 & ",2"
  31.                 Cells(I, "H").Value = "D," & Cells(I, "A").Value + 1 & ",3"
  32.                 Cells(I, "I").Value = "D," & Cells(I, "A").Value + 1 & ",4"
  33.                 Cells(I, "J").Value = "D," & Cells(I, "A").Value + 1 & ",5"
  34.             Else
  35.             End If
  36.         End If
  37.     Next
  38.    
  39.     Application.ScreenUpdating = True
  40.     Application.DisplayAlerts = True
  41.     Range("A11").Select
  42.    
  43.     Dim xlFolder As String
  44.     xlFolder = ThisWorkbook.Path & "\" & "i-Link ¸ô®|ÀÉ"        '«ü©w¸ê®Æ§¨
  45.     If Dir(xlFolder, vbDirectory) = "" Then MkDir xlFolder

  46.     Dim xPath As String
  47.     xPath = Application.ActiveWorkbook.Path
  48.     Application.ScreenUpdating = False
  49.     Application.DisplayAlerts = False
  50.     Titlename = ThisWorkbook.Sheets("»sµ{Àˬd°O¿ýªí").Range("H4").Value
  51.     With ActiveSheet
  52.        .Copy
  53.        ActiveSheet.DrawingObjects.Delete
  54.        X = InputBox("½Ð¿é¤JÀɦW!!", "¥t¦s·sÀÉ", Titlename & " " & "»sµ{(¸ô®|ÀÉ)")
  55.         If X <> "" Then
  56.             Application.ActiveWorkbook.SaveAs Filename:=xlFolder & "\" & X, _
  57.             FileFormat:=xlExcel8
  58.             MsgBox "Àx¦s¦¨¥\¡I"
  59.         ElseIf X = "" Then
  60.             MsgBox "¤w¨ú®øÀx¦s!!!"
  61.         End If
  62.        Application.ErrorCheckingOptions.BackgroundChecking = False
  63.        Application.ActiveWorkbook.Close False
  64.     End With
  65.    
  66.     For I = 11 To Range("B11").End(xlDown).Row
  67.         If Cells(I, "A") <> "" Then
  68.             Cells(I, "F").Value = ""
  69.             Cells(I, "G").Value = ""
  70.             Cells(I, "H").Value = ""
  71.             Cells(I, "I").Value = ""
  72.             Cells(I, "J").Value = ""
  73.         ElseIf Cells(I, "A") = "" Then
  74.             Exit For
  75.         End If
  76.     Next
  77.     For I = 47 To Range("A47").End(xlDown).Row
  78.         If Cells(I, "A") <> "" Then
  79.             Cells(I, "F").Value = ""
  80.             Cells(I, "J").Value = ""
  81.             Cells(I, "H").Value = ""
  82.             Cells(I, "I").Value = ""
  83.             Cells(I, "J").Value = ""
  84.         ElseIf Cells(I, "A") = "" Then
  85.             Exit For
  86.         End If
  87.     Next
  88.    
  89.     Application.DisplayAlerts = True
  90.     Application.ScreenUpdating = True
  91.    
  92.    
  93. End Sub
½Æ»s¥N½X
test2.rar (40.14 KB)

¥»©«³Ì«á¥Ñ n7822123 ©ó 2019-6-11 23:56 ½s¿è

¦^´_ 1# s13030029

³o¬qµ{¦¡!? §PÂ_­«½Æ....¥B¶]¤F100¦h¸U¦C...........

    For I = 47 To Range("A47").End(xlDown).Row
        If Cells(I, "B") <> "" Then
            If Cells(I, "B") <> "" Then
                Cells(I, "F").Value = "D," & Cells(I, "A").Value + 1 & ",1"
                Cells(I, "G").Value = "D," & Cells(I, "A").Value + 1 & ",2"
                Cells(I, "H").Value = "D," & Cells(I, "A").Value + 1 & ",3"
                Cells(I, "I").Value = "D," & Cells(I, "A").Value + 1 & ",4"
                Cells(I, "J").Value = "D," & Cells(I, "A").Value + 1 & ",5"
            Else
            End If
        End If
    Next

®É¶¡¥D­n¥d¦b§Aµ{¦¡½X27~38¦Cªº³¡¤À¡A

¦]¬°§Aµ¹ªºÀÉ®×±qA47¥H«á³£¨S¸ê®Æ¡A©Ò¥H¤£ª¾¹D³o¬qµ{¦¡¨ì©³­n·F¹À!

°j°é·|­«47¦C¶]¨ì1048576........¥BÀx¦s®æ³£¬OªÅ¥Õ¡Aµ¥©ó¥Õ¶]¤F100¦h¸U¦C!

¦pªG§A§â³o¬qµù¸Ñ±¼¡A·|µo²{³t«×°¨¤W´£ª@¡C
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 2# n7822123
§Úªºªí®æ·|¨ÌÀËÅ綵¥Ø¦pªG¶W¹L21¶µ¡A¤]´N¬O²Ä¤@±iªí¶ñº¡¤F¡A´N·|¦Û°Ê¼W¥[²Ä¤G±iªí®æ¦b¤U­±¡AÄ~Äò§â¸ê®Æ¶ñ¤W¡A©Ò¥H²{¦b47¦C¶}©l¤~·|¬OªÅªº

ªþÀÉ¡G test2.rar (56.35 KB)

TOP

¨ä¹ê¤£À³¸Ó¥ÎxlDown¹ï¤£¹ï?

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2019-6-12 09:00 ½s¿è

¦^´_ 4# s13030029


®¦®¦¡AÀ³¸Ó¨C±iªí®æ³£µ¹¥L¤@­Ó½d³ò¥h¶]´N¦n¤F

Á|¨Ò¨Ó»¡§A²Ä2±iªí®æ·|±qA47 ¨ìA67¡A´N§â§AªºI©T©w±q 47¨ì67´N¥i¥H¤F

¦pªG·|¦³²Ä3±iªí®æ»P²Ä4±iªí®æ¤]Ãþ¦ü³B²z¡A¤£¤Ó¥i¯à¶]¨ì1048576¦Cªº....
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2019-6-12 09:19 ½s¿è

¦^´_ 5# n7822123


¥i®Ú¾Ú¤@¨Çªí®æ©T©w·|¥X²{¦r²´¡A¥ý§PÂ_·|¦³´X­Óªí®æ¡A¦A³]¥ß½d³ò

Á|¨Ò :
¨C±iªí®æ©T©w¶]20¦C(²Ä¤@±i±q12¶]¨ì31)¡A¦³30±iªí®æ(ÅܼÆT)¡A
°²³]¨C±iªí®æªºÀx¦s®æ¦ì¸m¬Û®t37¦C¡A¥i¥H§ï¦¨¦p¤U

For T=1 to 30  '°²³]¦³30±iªí®æ
    For I = T*37-25 To T*37-6  '²Ä¤@¦C»P³Ì«á¤@¦C¨Ì¾Ú²ÄT±iªí®æ°µÅÜ°Ê
            If Cells(I, "B") <> "" Then
                Cells(I, "F").Value = "D," & Cells(I, "A").Value + 1 & ",1"
                Cells(I, "G").Value = "D," & Cells(I, "A").Value + 1 & ",2"
                Cells(I, "H").Value = "D," & Cells(I, "A").Value + 1 & ",3"
                Cells(I, "I").Value = "D," & Cells(I, "A").Value + 1 & ",4"
                Cells(I, "J").Value = "D," & Cells(I, "A").Value + 1 & ",5"
            Else
            End If
    Next I
Next T

·íµM³oÁÙ¬O¥i¥HÀu¤Æªº¡A¥Î°}¦C³B²z¥i¥H§ó§Ö¡C
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 6# n7822123


End(xlDown) ¥i¾A¥Î©ó ½T©w¦³¸ê®Æ ªº±¡ªp¤U¡AµL¸ê®Æ·|¶]¨ì³Ì¥½¦C

¨Ï¥Î«e­n¥ý§PÂ_¬O§_¦³¸ê®Æ
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 7# n7822123
­ì¨Ó¦p¦¹~~~«D±`·PÁÂ~~~

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD