[µo°Ý] ½Ð°Ý¦¹¬q¥¨¶°¦p¦ó²¤Æ©Î¥[§Ö³t«×??

t8899

Sub zzzzz()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
ActiveSheet.DisplayPageBreaks = False

With Sheets("Sheet6")

     Range("D16").Select
    Selection.Copy
    Range("b65536").End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

           Range("C16").Select
    Selection.Copy
    Range("O65536").End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P65536").End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

             Range("B16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q65536").End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
end with
end sub
---------------------------------------------------------------------------------------------
¥t¥~ ¦¹¨â­Ó¥i§_©ñ¦bwith ¸Ì­±
1.°õ¦æ¥t¤@­Ó¥¨¶°
2.Sheets("Sheet3").Range("Z2:Z111").Value = Sheets("Sheet3").Range("C2:C111").Value (¬O§_«D¦P¤@­Óª«¥ó?)

GBKEE

¦^´_ 13# acdx
¤u¨ã®Ñ¤@¯ë³£¬O¤@¼Ëªº,¦h¬Ý¦h½m²ß,¤é¤[­Ô¤@©w¥i¥H´À«á¤H¸Ñ´bªº.

acdx

¦^´_ 12# GBKEE
¨C¦¸¨Ó³oÃä¨D±ÏÁ`¬O¯à±qª©¤W¦U¦ì«e½úªº¸Ñµª¤¤Àò±o§Ú¥¼·Q¹Lªº¼gªk¡AÅý§Ú¤]±q¤¤¾Ç¨ì¤£¤Ö
¦b³o¸Ì¤]·Q½ÐG¤j¤¶²Ð´X¥»¤£¿ùªº¤u¨ã®ÑÅý§Ú¯à±q¤¤ºë¶i¦Û¤vªº¥\¤O
§Æ±æ±N¨Ó¦³¤@¤Ñ¤£¦A¥u¬O¤W¨Ó³oÃäµo°Ý¤]¯à¦VªO¤W´X¦ì¤j¤j¥i¥HÀ°¨ä¥L¤H¸Ñ´b ÁÂÁÂ

GBKEE

¦^´_ 11# acdx

1. Option Explicit
   
2. Private Sub format()
   
3.     Dim ws As Worksheet, sName As String, AR(1 To 2), I As Integer
   
4.     sName = "PTAVS"
   
5.     On Error Resume Next
   
6.     Set ws = Sheets(sName)
   
7.     If ws Is Nothing Then
   
8.         Worksheets.Add after:=Worksheets(Worksheets.Count)
   
9.         Worksheets(Worksheets.Count).Name = sName
   
10.         Set ws = Sheets(sName)
    
11.     Else
    
12.         MsgBox sName & "¤u§@ªí¤w¦s¦b¡C"
    
13.         Sheets("Result").Select
    
14.         Exit Sub
    
15.     End If
    
16.     With ws.Cells
    
17.         .HorizontalAlignment = xlCenter
    
18.         .VerticalAlignment = xlCenter
    
19.         .Orientation = 0
    
20.         .AddIndent = False
    
21.         .IndentLevel = 0
    
22.         .ShrinkToFit = False
    
23.         .ReadingOrder = xlContext
    
24.         .MergeCells = False
    
25.         With .Font
    
26.             .Name = "Arial"
    
27.             .Size = 10
    
28.             .Strikethrough = False
    
29.             .Superscript = False
    
30.             .Subscript = False
    
31.             .OutlineFont = False
    
32.             .Shadow = False
    
33.             .Underline = xlUnderlineStyleNone
    
34.             .ColorIndex = xlAutomatic
    
35.             .TintAndShade = 0
    
36.             '.ThemeFont = xlThemeFontNone  '2003¨S³o°Ñ¼Æ
    
37.         End With
    
38.         .Range("B1:M1").Merge
    
39.         AR(1) = Array("A1:A4", "B2:G2", "B3:D3", "E3:G3", "B4:G4")
    
40.         AR(2) = Array("C1~C5", "(sone)", "H", "M", "")
    
41.         For I = 0 To UBound(AR(1))
    
42.             With .Range(AR(1)(I))
    
43.                 .WrapText = False
    
44.                 .MergeCells = IIf(I < UBound(AR(1)), True, False)
    
45.                 .Value = AR(2)(I)
    
46.             End With
    
47.         Next
    
48.         .Range("B4:D4").Value = Array("mean", "standard deviation", "mean+CV*stdev")
    
49.         .Range("B4:D4").Copy .Range("E4")
    
50.         .Range("B2:G4").Copy .Range("H2")
    
51.         .Range("H2").Value = "(tu)"
    
52.         With .Range("A1:M4")
    
53.             For I = 5 To 12   'µe®Ø½u
    
54.                 With .Borders(I)
    
55.                     .LineStyle = IIf(I >= 7, xlContinuous, xlNone)
    
56.                     If I >= 7 Then .ColorIndex = 0
    
57.                     'If I >= 7 Then .TintAndShade = 0       '2003®Ø½u¨S³oÄÝ©Ê
    
58.                     If I >= 7 Then .Weight = xlThin
    
59.                 End With
    
60.             Next
    
61.             .Font.Name = "Arial"
    
62.             .Font.Size = 10
    
63.         End With
    
64.     End With
    
65.     Sheets("Result").Select
    
66. End Sub

½Æ»s¥N½X

acdx

¦^´_ 2# GBKEE
§Ú¤]¦³¤@¬q¥¨¶°¸Ó¦p²¤Æ?
Private Sub format()

Dim ws As Worksheet
Dim sName As String

sName = "PTAVS"
On Error Resume Next
Set ws = Sheets(sName)

If ws Is Nothing Then
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = sName
    ws.Activate
Else
    MsgBox sName & "¤u§@ªí¤w¦s¦b¡C"
    Sheets("Result").Select
    Exit Sub
End If

Cells.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With

Range("B1:M1").Merge
    Range("A1:A4").Select
    With Selection
        .WrapText = False
        .MergeCells = True
        .Value = "C1~C5"
    End With
   
    Range("B2:G2").Select
    With Selection
        .WrapText = False
        .MergeCells = True
        .Value = "(sone)"
    End With
   
    Range("B3:D3").Select
    With Selection
        .WrapText = False
        .MergeCells = True
        .Value = "H"
    End With
   
    Range("E3:G3").Select
    With Selection
        .WrapText = False
        .MergeCells = True
        .Value = "M"
    End With
   
    Range("B4:G4").Select
    With Selection
        .WrapText = True
        .MergeCells = False
    End With
    Range("B4").Value = "mean"
    Range("C4").Value = "standard deviation"
    Range("D4").Value = "mean+CV*stdev"
   
    Range("B4:D4").Copy
    Range("E4").Select
    ActiveSheet.Paste
   
    Range("B2:G4").Copy
    Range("H2").Select
    ActiveSheet.Paste
   
    Range("H2").Value = "(tu)"
   
    Range("A1:M4").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 10
    End With
Sheets("Result").Select
End Sub
'-------------------------------------
³o¬qcode¦³«Ü¤jªº³¡¤À¬O¦b°µÀx¦s®æªº¦X¨Ö¥H¤Îµe®Ø½u ³o¸Ó¦p¦ó²¤Æ©O?

GBKEE

¦^´_ 9# t8899
´N³o¼Ë¦h½m²ß,·|¶i¨Bªº
§ó¥¿(¿N»æ¦Y¦h¤F)

1. Range("F11").NumberFormatLocal = .NumberFormatLocal      '¼Æ¦r®æ¦¡

½Æ»s¥N½X

t8899

¦^´_  t8899
¬OWith Sheets("Sheet1") ¸ÌªºCells(i, "h") ´N¥i¥ÎWith. (§Ö¤£§Ö??? ¤£ª¾§Aªº·Qªk)
GBKEE µoªí©ó 2013-9-6 10:55

  Range("F10").Formula = .Formula           '¤½¦¡
  Range("F11").FormulaLocal = .FormulaLocal  '¼Æ¦r®æ¦¡
¸Õªºµ²ªG,³o¨â­Ó³£¬O¤½¦¡????
³o¤U­±¨â­Óªº»yªk?
1.copy ­È»P¼Æ¦r®æ¦¡
2.copy ®æ¦¡

Sub Macro1()
With Range("D2")
Range("B65536").End(xlUp).Offset(1) = ????
End With
End Sub

GBKEE

¦^´_ 7# t8899
¬OWith Sheets("Sheet1") ¸ÌªºCells(i, "h") ´N¥i¥ÎWith. (§Ö¤£§Ö??? ¤£ª¾§Aªº·Qªk)

t8899

¦^´_  t8899 IsError(Cells(i, "h"))  ¬O With Sheets("Sheet1") ¸ÌªºCells(i, "h") ¶Ü?,
¤£¬O­n«ü©ú¤u§@ªí ...
GBKEE µoªí©ó 2013-9-6 10:23

IsError(Cells(i, "h"))  ¬O With Sheets("Sheet1") ¸ÌªºCells(i, "h") ¶Ü?  

¬Oªº!

Run "clear"  ³o¬O VBAªºÃöÁä¦r Clear ¤èªk,³Ì¦n´«­Ó¦r¦ê  

ÁÂÁÂ!¤w§ï¬° ACLEAR

¦³µL¥²­n¥ÎWITH ? §Ú¥Îªºµ²ªG·Pı¤£¥X¦³¤ñ¸û§Ö

GBKEE

¦^´_ 5# t8899

1. For i = 350 To 21 Step -1
   
2.   If IsError(Cells(i, "h")) Then Run "clear": Exit For
   
3. next

½Æ»s¥N½X

IsError(Cells(i, "h"))  ¬O With Sheets("Sheet1") ¸ÌªºCells(i, "h") ¶Ü?,
¤£¬O­n«ü©ú¤u§@ªí  IsError(Sheets("¤u§@ªí¦WºÙ").Cells(i, "h"))

Run "clear"  ³o¬O VBAªºÃöÁä¦r Clear ¤èªk,³Ì¦n´«­Ó¦r¦ê
¥t¥~ Run ªºµ{§Ç¦bª«¥ó¼Ò²Õ(¤£¦b¤@¯ë¼Ò²Õ¸Ì)      ­n«ü©ú¼Ò²Õ Run "Sheet1.µ{§Ç"