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

[µo°Ý] ¤£¦PªºSheet°õ¦æ­«ÂЪº¥¨¶°

[µo°Ý] ¤£¦PªºSheet°õ¦æ­«ÂЪº¥¨¶°

¦U¦ì¥ý¶i¦n,
§Ú¦bExcel¤u§@ªí¤¤¨C¦¸¦³·s­q³æ´N·|¼W¥[¤@­Ó·sªº¤ë¥÷ªºSheet,¨Ã¥H­q³æ©ÒÄݪº¤ë¥÷¬°Sheet©R¦W,¨Ò¦p²{¦b¬O10¤ë«h©R¦W¬°"Oct",11¤ë¥÷ªº­q³æ´N¥t¶}¤@­ÓSheet,©R¦W¬°"Nov"....,¥H¦¹Ãþ±À,¦Ó¥B¤u§@ªí¤¤ªº®æ¦¡³£¬Û¦P,¥H¤U¥¨¶°¬O§Ú°µªº¨ä¤¤¤@­Ó°Ê§@,¦]¬°Á`¦@¦³12­Ó¤ë¥÷,§Ú´N­n¬°copy©Ò¦³ªº¥¨¶°,¦A­×§ï"Sheets"ªº¦WºÙ.
¤u§@ªí¤ë¥÷¶V¤jªº§Ú·|©ñ¦b¥kÃä,¤]´N¬O¦p¦P¤@¯ë·s¼WªºSheet¤@¼Ë,·|¦Û°Ê·s¼W¦b¥kÃä,¦³¨S¦³¤@­Ó¥¨¶°ªº¼gªk,¥i¥HÅýSheet¦b"Jan"¥kÃ䪺,¦Û°Ê°õ¦æ¥H¤U¤@¼Ò¤@¼Ëªº¥¨¶°,¦Ó¤£¬O±N¥¨¶°copy 12¦¸¦A§ïSheetªº¦WºÙ?
¦^ÂЮɽХH¤U¦Cªº¥¨¶°°µ­×§ï,§_«h§Ú¤£¤Ó·|®M¥Î,ÁÂÁÂ!
    Sheets("Oct").Select
    Range("O20").Select
    ActiveCell.FormulaR1C1 = "=ROUND(RC10*RC12,2)"
    Range("O20").Select
   
    ActiveWorkbook.Save

§A¸Ó§âµ{¦¡©ñ¦b¤@¯ë¼Ò²Õ¡A¤£¬O¤u§@ªí¼Ò²Õ¡C
    Range("O20").Select
    ActiveCell.FormulaR1C1 = "=ROUND(RC10*RC12,2)"
      ActiveWorkbook.Save
³o¼Ë´N¦æ¤F¡Aµ{¦¡·|§@¥Î¦b·í«e¶}±Òªº¤u§@ªí

TOP

¤£¦n·N«ä,§Ú¤£¤F¸Ñ§A»¡ªº"§âµ{¦¡©ñ¦b¤@¯ë¼Ò²Õ¡A¤£¬O¤u§@ªí¼Ò²Õ"¬O¤°»ò·N«ä?
¤£¹L§Ú·Ó§A¼gµ¹§Úªº¥¨¶°¥h°õ¦æ¬OµL§@¥Îªº,¦]¬°§Úªº¥¨¶°«ü¥O¬O¥Î¿ýªº¤£¬O¥Î¼gªº,¸Ì­±¦³«Ü¦h«ü¥O,¨Ò¦p±Æ§Ç....,¥B¬O¥H¨ä¤¤¤@­Ó¤ë¥÷¬°½d¥»,¨ä¾lªº·Ócopy,¥u­×§ïsheet¦WºÙ,¨Ò¦p§Ú¬O¥H"Sep"¥h¿ý»s,¸Ì­±·|¥X²{«Ü¦h¦pWorksheets("Sep")ªº¦WºÙ,©Ò¥H§A§¹¥þ¤£¥X²{¤u§@ªí¦WºÙ¬OµLªk°õ¦æ¥¨¶°ªº.

TOP

©Ò¥H§A§¹¥þ¤£¥X²{¤u§@ªí¦WºÙ¬OµLªk°õ¦æ¥¨¶°ªº.

¬JµM§A³o»ò»{¬°¡A´N¤£ª¾¸Ó¦p¦óÀ°§A¤F¡C

TOP

¤£ª¾¹D¬O§_ÁÙ¦³¨ä¥L¤è¦¡,¥i¥H¥O¥kÃ䪺¦h­Ósheet,°õ¦æ¦P¤@­Ó¥¨¶°?

TOP

¥»¨Ó¥¨¶°´N¥u¼g¤@­Ó¡A¨C­Ó¤u§@ªí³£¥i¨Ï¥Î¡C
§A¤@©w­n¦b¥¨¶°¤W«a¤u§@ªí¦W´N¨S¿ìªk¤F¡C

TOP

¦^´_ 1# PJChen
³o¼Ë¥i¥H¶Ü?
  1. Sub Ex() '¤u§@ªí¤ë¥÷¶V¤jªº§Ú·|©ñ¦b¥kÃä -> ¥¦ªº¯Á¤Þ­È (Sheets.Count)
  2.     With Sheets(Sheets.Count)
  3.         .Range("O20").FormulaR1C1 = "=ROUND(RC10*RC12,2)"
  4.     Next
  5.     ActiveWorkbook.Save
  6. End Sub
  7. Sub Ex1() '¥i¥HÅýSheet¦b"Jan"¥kÃ䪺,¦Û°Ê°õ¦æ¥H¤U¤@¼Ò¤@¼Ëªº¥¨¶° ->©Ò¦³ªº¤u§@ªí°õ¦æ¤@¼Ò¤@¼Ëªº¥¨¶°
  8.     Dim Sh As Worksheet
  9.     For Each Sh In Sheets
  10.         Sh.Range("O20").FormulaR1C1 = "=ROUND(RC10*RC12,2)"
  11.     Next
  12.     ActiveWorkbook.Save
  13. End Sub
½Æ»s¥N½X
¦^´_ 3# PJChen
§Ú¤£¤F¸Ñ§A»¡ªº"§âµ{¦¡©ñ¦b¤@¯ë¼Ò²Õ¡A¤£¬O¤u§@ªí¼Ò²Õ
¦p¹Ï:

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-10-20 22:08 ½s¿è
¤£¦n·N«ä,§Ú¤£¤F¸Ñ§A»¡ªº"§âµ{¦¡©ñ¦b¤@¯ë¼Ò²Õ¡A¤£¬O¤u§@ªí¼Ò²Õ"¬O¤°»ò·N«ä?
¤£¹L§Ú·Ó§A¼gµ¹§Úªº¥¨¶°¥h°õ¦æ ...
PJChen µoªí©ó 2011-10-16 22:31

·í§AÂIÀ»¥ô¤@²{¦s¤u§@ªí±µµÛÂI¿ï "À˵øµ{¦¡½X" «á©Ò¬Ý¨ìªºµ{¦¡´N¬O¦ì©ó "¤u§@ªí¼Ò²Õ" ¤º,
¤@¯ë¨Ó»¡¥þ·sªº Excel Àɮפº¨Ã¤£·|¦Û°Ê²£¥Í "¤@¯ë¼Ò²Õ",
·í§Ú­ÌÂIÀ»¤F "´¡¤J" ¦AÂI "¼Ò²Õ" ¤§«á¥X²{ªº Module1 ´N¬O "¤@¯ë¼Ò²Õ",
­Y¦AÄ~Äò·s¼W Module2 ... µ¥³o¨Ç³£¬O "¤@¯ë¼Ò²Õ".

¦^´_ 1# PJChen
§Aªº»Ý¨D°ò¥»¤WÁÙ¬O¥i¥H°µ¨ìªº,
ÃöÁä¦b©ó©I¥s¤@¯ë¼Ò²Õµ{§Ç®É­n§â¼ÐªºSheet¶Ç»¼µ¹¸Ó¼Ò²Õµ{§Ç¥H«K¨ä¨Ï¥Î.
¦]¬°§Aªº¨Ò¤l¤¤³£¬O°w¹ï Range("O20") °µ³B²z,
¬G¦Ó§Ú­Ì¥i¥H¶È¶Ç»¼ Range("O20")  ¹L¥h¨Ï¥Î.
(¤@¯ë¼Ò²Õ)
  1. Sub SetData(rTarget As Range)
  2.   With rTarget
  3.     .FormulaR1C1 = "=ROUND(RC10*RC12,2)"
  4.     .Select
  5.     ActiveWorkbook.Save
  6.   End With
  7. End Sub
½Æ»s¥N½X
(¦U¤u§@ªí¼Ò²Õ) (°²³]­n¦b SelectionChange ¤º©I¥s¦@¥Îµ{§Ç)
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.   Call SetData(Range("O20"))
  3. End Sub
½Æ»s¥N½X
¤£¦PªºSheet°õ¦æ­«ÂЪº¥¨¶°-ans.zip (7.08 KB)
(«á°O : µ{¦¡¤W¶Ç«á¤~µo²{¥´¦r®É»~Key SetData ¬° SetDate ½Ð¦Û¦æ­×§ïµ{§Ç¦WºÙ)

TOP

·PÁ¦U¦ì¥ý¶iªº¦^ÂÐ,
§Ú¨Ï¥Îªº¥¨¶°³£¬O¥Î¿ý»sªº,©Ò¥H¦U¦ì¼gµ¹§Úªº,§Ú¯uªº¬Ý¤£À´,¤£¹L§Ú¨Ì"oobird"±Ð§Úªº¤èªk,¦b¥¨¶°¤¤¤£­n«a¤W¤u§@ªíªº¦WºÙ,©Ò¥H§Ú±N¨ä¤¤¤@­Ó¤ñ¸ûµuªº¥¨¶°¥ý±N©Ò¦³¤u§@ªí¦WºÙ¥h°£«á°õ¦æ¥¨¶°,¥i¬O¥X²{¹ï¸ÜàF"½sĶ¿ù»~:§ä¤£¨ì¤èªk©Î¸ê®Æ¦¨­û"§Ú¤£¤F¸Ñ¥¦ªº·N«ä¬O¤°»ò,¤£¹L¦³½bÀY«üµÛ¥¨¶°ªº²Ä¤@¦æ,Åã¥Ü¦³°ÝÃD,¥Ñ©ó§Ú¤§«e°Ýªº°ÝÃD¥u¬OºI¨ú¤@¤p¬q¥¨¶°¤º®e,¦Ó¤j®a¦^Âе¹§Úªº,§Ú¤S¤£·|®M¥Î,©Ò¥H§Ú±N§¹¾ãªº¥¨¶°¼g©ó¤U,§Æ±æ¼Ó¤W¦³¦^Âе¹§Úªº¦UºØ¤è¦¡³£À°§Ú®M¤J§¹¾ãªº¥¨¶°¤¤,§Ú¦A¹E¤@¸Õ¸Õ¬Ýµ²ªG¦p¦ó?¤£¬O§Ú°½Ãi,§Ú¯uªº¬Ý¤£À´.
Sub sorting_chart()
'
' sorting_chart ¥¨¶°
'

'
    Windows("2011 BCMart Chart.xls").Activate
   
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("U4").Select
    ActiveWorkbook.Worksheets.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets.Sort.SortFields.Add Key:=Range("U5:U3000"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets.Sort.SortFields.Add Key:=Range("Q53000"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets.Sort.SortFields.Add Key:=Range("A5:A3000"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets.Sort
        .SetRange Range("A4:AA3000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.SaveAs Filename:= _
        ":\BCM\2011 BCMart Chart\2011 BCMart Chart-sorted-2011.xls", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

End Sub

TOP

        ÀR«ä¦Û¦b : §ïÅܦۤv¬O¦Û±Ï¡A¼vÅT§O¤H¬O±Ï¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD