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

[µo°Ý] ¦p¦óÅýSheet2=I3¡AµM«á¤SÅý¸ÓÀɦW¥i¥HÄ~ÄòÅܧó?

[µo°Ý] ¦p¦óÅýSheet2=I3¡AµM«á¤SÅý¸ÓÀɦW¥i¥HÄ~ÄòÅܧó?

¥»©«³Ì«á¥Ñ av8d ©ó 2012-10-15 13:59 ½s¿è

Sub aa()
Sheets("Sheet2").Name = [I3]
End Sub


Sheet2ÀɦW«h·|Åܦ¨I3¡A§Ú¸Ó¦p¦óÅýI3Ä~ÄòÅܧó?

¦^´_ 1# av8d


    °Ñ¦Ò¬Ý¬Ý

¬¡­¶Ã¯1.rar (9.34 KB)
  1. Sub ¥¨¶°1()

  2. aois = ActiveSheet.Range("A2").CurrentRegion.Rows.Count
  3.     Sheets("¤u§@ªí1").Name = "Á`ªí"

  4. For gaa = 2 To aois
  5.    
  6.     Sheets.Add After:=Sheets(Sheets.Count)
  7.     Sheets("¤u§@ªí" & gaa - 1).Select
  8.     Sheets("¤u§@ªí" & gaa - 1).Name = ¤u§@ªí1.Cells(gaa, 1)

  9. Next

  10. End Sub
½Æ»s¥N½X

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¦p¦ó"¦Û°Ê¿z¿ï"+"¦s¨ì·s¤u§@ªí"

test.rar (14.73 KB)

¦Û°Ê¿z¿ï²Ä¤G¦C·|®ø¥¢~¨ä¾l¥¿±`~

¿z¿ï«á~±NOÄæ¦Û°Ê¥[Á`©ó¤U¤è~

±N²Ä¤G¦C¨ì¥[Á`¦C½Æ»s¶K¨ì~·s¤u§@ªí~¤u§@ªí¦WºÙ=I3

­Y¥Ø«e¦³¸Ó¤u§@ªí«hÂл\¶K¤W~
-----------------------------------------------------
¥Ø«e¥u¬ã¨s¥XSheet2©R¦W
Sheets("Sheet2").Name = [I3]

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-10-16 10:59 ½s¿è

¦^´_ 3# av8d
¬O³o¼Ë¶Ü?
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Car_No As String, xlRow  As Long
  4.     With Sheet1
  5.         .Activate
  6.         .AutoFilterMode = False
  7.         Car_No = InputBox("½Ð¿é¤J¨®¸¹")
  8.         If Car_No = "" Then MsgBox "¨S¿é¤J ¨®¸¹ ??" & Car_No: Exit Sub
  9.         .Rows(2).Cells(1).AutoFilter Field:=9, Criteria1:=UCase(Car_No)
  10.         xlRow = .Rows(2).Cells(9).End(xlDown).Row
  11.         If xlRow <> Rows.Count Then
  12.             .Cells.SpecialCells(xlCellTypeVisible).Copy
  13.         Else
  14.             MsgBox "§ä¤£¨ì ¨®¸¹ !! " & Car_No:      Exit Sub
  15.         End If
  16.         With Sheets.Add(, Sheets("sheet1"))
  17.             .Paste
  18.             Application.CutCopyMode = False
  19.             .Name = .[i3]
  20.             .[a1].Select
  21.         End With
  22.           '.Rows(xlRow).Delete   '§R¤@¦æ¸ê®Æ
  23.            'http://forum.twbts.com/viewthread.php?tid=6706&rpid=45209&ordertype=0&page=7#pid45209      
  24.            '´£¿ô­×§ï  §R¦h¦æ¸ê®Æ
  25.             .Cells.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Offset(2).Delete xlUp
  26.         .AutoFilterMode = False
  27.         .Activate
  28.     End With
  29. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# GBKEE


    test2.rar (12.75 KB)

®M¥Î¤F¤@¤U¤j¤jªº~·Q½Ð¤j¤j¦AÀ°¦£¤@¤U~ÁÂÁÂ

·í¸ê®Æ¥t¦s·sÀÉ«á~Sheet1ªº¸ê®Æ­Y¦³3¤ñ~¥L¥u·|²¾°£1¤ñ~¬O§_¥i¥H¤£²¾°£©Î²¾°£3¤ñ

·í¸ê®Æ¥t¦s·sÀÉ«á~·s¸ê®Æªºª÷ÃB­Y­n¥[Á`¸Ó¦p¦ó°µ?

§Ú­×§ï¤F¤@­Ó~¤w·s¼W¸ê®Æªí?­Ó~¤£ª¾¹D¬O¤£¬O¨S¼g¦n~¥L¤£·|¦Û°Ê¼W¥[

TOP

¦^´_ 4# GBKEE
§Ú¦A¦¸­«·s DEBUG ¤@¦¸¡Aµ²ªG¬O¡G
02.PNG
2012-10-16 11:03

ÁöµM¦b¹ê°È¤W§Úª¾¹D­n¦p¦ó³B­ù¡A¦ý¤H¬¡µÛ´N¬O­n¦h¦h¾Ç²ß¡A
¤£¼ô±xªº»yªk­n²`¤Á¥hÁA¸Ñ¡A¤£¬O¹À¡H

TOP

¦^´_ 6# c_c_lai


    ¾Ç²ß¤F~ÁÂÁ¤j¤jªº´£¥Ü~§Ú·|¥[­¿§V¤Oªº!

TOP

¦^´_ 5# av8d
  1. Dim d As Object
  2. Private Sub UserForm_Initialize()
  3.     Dim A As Range
  4.     Set d = CreateObject("Scripting.Dictionary")
  5.     With Sheets("Sheet1")
  6.         .Activate
  7.         For Each A In .Range("I3", .[i3].End(xlDown))
  8.           d(A.Value) = IIf(d(A.Value) = "", A.Offset(, 1).Value, d(A.Value) & "," & A.Offset(, 1))
  9.         Next
  10.         ComboBox1.List = d.keys
  11.     End With
  12.     ¨®¸¹¤u§@ªí¼Æ
  13. End Sub
  14. Private Sub ComboBox1_Change()   ' ´_­ì¿z¿ï
  15.     ActiveSheet.AutoFilterMode = False
  16. End Sub
  17. Private Sub CommandButton1_Click() ' ´_­ì¿z¿ï
  18.     ctiveSheet.AutoFilterMode = False
  19. End Sub
  20. Private Sub CommandButton2_Click()
  21.     Call Ex
  22.     ¨®¸¹¤u§@ªí¼Æ
  23. End Sub
  24. Private Sub ¨®¸¹¤u§@ªí¼Æ()
  25.     TextBox1 = Sheets.Count - 1
  26. End Sub
½Æ»s¥N½X
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Car_No As String, xlRow As Long, E As Range, Rng As Range
  4.     With Sheet1
  5.         .Activate
  6.         .AutoFilterMode = False
  7.         Car_No = UserForm1.ComboBox1
  8.         .Rows(2).Cells(1).AutoFilter Field:=9, Criteria1:=UCase(Car_No)
  9.         xlRow = .Rows(2).Cells(9).End(xlDown).Row
  10.         If xlRow <> Rows.Count Then
  11.             Set Rng = .Cells.SpecialCells(xlCellTypeVisible)
  12.         Else
  13.             MsgBox "§ä¤£¨ì ¨®¸¹ !! " & Car_No:      Exit Sub
  14.         End If
  15.         '**********
  16.         On Error Resume Next                '±Ò°Êªº¿ù»~³B²zµ{¦¡: ¦³¿ù»~Ä~Äò¤U¤@¦æµ{¦¡½X
  17.         Sheets(Car_No).Activate      '¨S¦³¨®¸¹¤u§@ªí·|¦³¿ù»~
  18.         If Err.Number <> 0 Then             'µ{¦¡¦³¿ù»~
  19.             Sheets.Add , Sheets("sheet1")   '·s¼W¤u§@ªí
  20.             '***®ø°£ ¦³¿ù»~Ä~Äò¤U¤@¦æµ{¦¡½Xªº«ü¥O***
  21.             On Error GoTo 0                 '°±¤î²{¦bµ{§ÇùØ¥ô¦ó¤w±Ò°Êªº¿ù»~³B²zµ{¦¡¡C
  22.         End If
  23.         '**********************
  24.         With ActiveSheet
  25.             .Cells.Clear
  26.             Rng.Copy .[a1]
  27.             Application.CutCopyMode = False
  28.             .Name = .[i3]
  29.             .Cells(.Rows.Count, "O").End(xlUp).Offset(1, -1) = "¦X­p"
  30.             .Cells(.Rows.Count, "O").End(xlUp).Offset(1) = Application.Sum(.Range("O:O"))    'ª÷ÃB¥[Á`
  31.         End With
  32.         
  33.         '--***  ¬O§_¥i¥H¤£²¾°£©Î²¾°£3µ§---
  34.         If UserForm1.OptionButton1 Then   '***ªí³æ·s¼W OptionButton1 ±±¨î¶µ .Value=True
  35.             For Each E In Rng.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).EntireRow
  36.                 If E.Row > 2 Then E.Delete
  37.             Next
  38.         End If
  39.         '-- ****************
  40.         .AutoFilterMode = False
  41.         .Activate
  42.     End With
  43. End Sub
½Æ»s¥N½X
¦^´_ 6# c_c_lai
.Cells.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Offset(2).Delete xlUp
¬O¦³¿ù»~ªº¤w­×¥¿ ¸Õ¸Õ¬Ý

TOP

¦^´_ 8# GBKEE


    ¾Ç²ß¤F¡A§Ú¯uªº¤~²¨¾Ç²L¡A§Ú­n¥[­¿§V¤O!

TOP

        ÀR«ä¦Û¦b : µoµÊ®ð¬Oµu¼ÈªºµoºÆ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD