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

[µo°Ý] ¦p¦ó¥ÎVBA §âSUBOROUTINE¼g¤J¯S©wWORKSHEET¡H

¦^´_ 20# lpk187


    ¦³§r¡A§Ú¥H¬°¬O³o¼Ë¡G¥Ñ©óµLªk­«³]¸Ó¤u§@ªíªºUSEDRANGE¡A©Ò¥H¨C¦¸¦bUSEDRANGE¤¤¶¡insert¤F10Äæ¡Ausedrange³£·|Åܤj10Äæ
§A¥i¥H¸Õ¸Õ¥Î§ÚªºV2¡A³ÌªìªºUSEDRANGE¬O¡u$A$1:$S$31¡v¡AµM«á¥þ¿ï¦A¥þ³¡²M°£¸ê®Æ¡AUSEDRANGEÅܦ¨¡u$C$1:$S$1¡v¡A©Ò¥H¨C¦¸µ{¦¡INSERT COLUMNS¡A³£·|¥OUSEDRANGEÅܤj¡C¦Ó¦pªG§âinsert¨º¥y§R¥h¡Aµ{¦¡«K·|§â©Ò¦³¸ê®Æ¼g¦bB¦ÜDÄæ¤W¡AUSEDRANGE«K¨S¦³Åܤj
¤£ª¾¹D¬O¤£¬O³o¼Ë¡A§Ú¬O³o¼Ë¸ÑÄÀ¡C

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-10-13 16:08 ½s¿è

¦^´_ 21# ¤p«L«È

¦³®É­Ô¡A¸ÕÅç¤@¤U´Nª¾¹D¡A¤U­±§A¸Õ¤@¤U´Nª¾¹D¤£·|¼W¥[¡I
  1. Set LayoutRS = ConfigCN.Execute(LayoutSQL)
  2.     B = 2
  3.     C = 3
  4.     D = 4
  5.    
  6. For i = StartYear To EndYear
  7.     'Out.Columns("B:D").Insert
  8.     Out.Range(Columns(C), Columns(D)).Columns.Group
  9.     Out.Cells(1, B) = i & " / FY"
  10.     Out.Cells(1, C) = i & " / 2H"
  11.     Out.Cells(1, D) = i & " / 1H"
  12.    
  13.     With LayoutRS
  14.         .MoveFirst
  15.         
  16.         Do Until .EOF
  17.             If Out.Cells(.Fields("Item_ID"), 1) = "" Then
  18.                 Out.Cells(.Fields("Item_ID"), 1) = .Fields("Item_Name").Value
  19.                 'End If
  20.             End If
  21.             .MoveNext
  22.         Loop
  23.     End With
  24.    

  25.     DataSQL = "select * from tbl_Income_Sub where Code = " & Code & " and S_Year = '" & i & "'"
  26.     Set DataRS = DataCN.Execute(DataSQL)
  27.     With DataRS
  28.     Do Until .EOF
  29.         Select Case .Fields("Term")
  30.             Case "1H"
  31.                 TargetCol = D
  32.             Case "FY"
  33.                 TargetCol = B
  34.         End Select
  35.         Out.Cells(2, TargetCol) = .Fields("Currency")
  36.         Out.Cells(3, TargetCol) = .Fields("Unit")
  37.         Out.Cells(4, TargetCol) = .Fields("Report_Date")
  38.         CurrUnit = .Fields("Unit")
  39.         If TargetCol = B Then
  40.             Out.Cells(2, TargetCol + 1) = .Fields("Currency")
  41.             Out.Cells(3, TargetCol + 1) = .Fields("Unit")
  42.             Out.Cells(4, TargetCol + 1) = .Fields("Report_Date")
  43.         End If
  44.         .MoveNext
  45.     Loop
  46.     End With
  47.    
  48.     DataSQL = "select * from tbl_Income where Code = " & Code & " and S_Year = '" & i & "'"
  49.     Set DataRS = DataCN.Execute(DataSQL)
  50.    
  51.     With DataRS
  52.     Do Until .EOF
  53.         If Not Out.Columns(1).Find(.Fields("Item"), lookat:=xlWhole) Is Nothing Then
  54.             TargetRow = Out.Columns(1).Find(.Fields("Item"), lookat:=xlWhole).Row
  55.             Select Case .Fields("Term")
  56.                 Case "1H"
  57.                     TargetCol = D
  58.                 Case "FY"
  59.                     TargetCol = B
  60.             End Select

  61.             Out.Cells(TargetRow, TargetCol) = Round(.Fields("Amount"), 4)
  62.         End If
  63.         .MoveNext
  64.     Loop
  65.     End With
  66.     B = B + 3
  67.     C = C + 3
  68.     D = D + 3

  69. Next i
½Æ»s¥N½X

TOP

¦^´_ 22# lpk187
­«ÂI¤£¬O³o­Ó§a ...
²{¦b¬O¦b»¡©ú©ú¨S¸ê®Æ¨S®æ¦¡ªºÀx¦s®æ¡Ausedrange ¦b¬Y¨Ç±¡ªp·|»~§P¬°¦³¨Ï¥Î¡C
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 22# lpk187


ÁÂÁ§Aªº«ü±Ð¡Aªº½T¦p¦¹¡AÁöµM³o­Ó°µªk¬O¥i¥H¥OUSEDRANGE¤£Åܤj¡A¦ýUSEDRANGEªº¸ê®Æ¤´µM¤£¥¿½T
¦pªG°õ¦æ¤F§A´£¨Ñªº¥N½X«á¡A¤â°Ê§â©Ò¦³ªº¸ê®Æ§R°£¡AUSEDRANGE¤´µMÅã¥Ü¡G$C$1:$T$1

TOP

¦^´_ 24# ¤p«L«È


    ¤£¦n·N«ä¡I­ì¨Ó§Ú¨S¬Ý«e­±ªº°Q½×¡A¯u©êºp¡I
§Ú¸Õ¤F¤£¤Ö¤èªk¡A³Ì«á¦³µo²{¡A¦bµ²§ô¬¡­¶Ã¯¤§«e²MªÅ¤u§@ªí¡C¦A¥´¶}ªº®É­Ô¡A´N·|«ì´_Âk¹sªºUsedRange¡C§A¥i¥H¸Õ¸Õ¬Ý¡I

TOP

¦^´_ 24# ¤p«L«È


    ¬Ý¬Ý¤U¦Cµ{¦¡¯à¤£¯à¾A¦X¨Ï¥Î
  1. Sub CompactSheet()
  2.     Dim ws As Worksheet
  3.     Dim R%, C%
  4.     Set ws = Sheets("Output")
  5.     With ws
  6.         ws.[A1].Select
  7.         Debug.Print .UsedRange.Address
  8.         On Error Resume Next
  9.         .Cells.Ungroup
  10.         .Cells.EntireColumn.Hidden = False
  11.         .Cells.Delete
  12.         
  13. 'Copy ¤@°Ï¨S¦³ÁôÂùLªº Columns ¨ì .UsedRange

  14.         [A1].Select
  15.         Debug.Print .UsedRange.Address
  16.         C = .UsedRange.Columns.Count
  17.         Range("A1").Resize(.Rows.Count, C).Copy .UsedRange
  18.         [A1].Select
  19.         Debug.Print .UsedRange.Address
  20.         On Error GoTo 0
  21.     End With
  22. End Sub
½Æ»s¥N½X

TOP

¦^´_ 26# Scott090

ÁÂÁ§A¡A§Ú§â§Aªº¥N½X½Æ»s¤@¦¸¡A·í¦¨RESET USEDRANGE.ADDRESS¡A¦ý¥i±¤¥¼¯à¦¨¥\¡C
¥i¯à¬OexcelªºBUG¡AµLªk¸Ñ¨M...

TOP

¦^´_ 27# ¤p«L«È


    ³o­Ó¥Î 13# ªºªþ¥ó¸Õ¹L
  1. Sub CompactSheet()
  2.     Dim ws As Worksheet
  3.     Dim C%, Col%
  4.     Set ws = Sheets("Output")
  5.     ws.Select
  6.     With ws
  7. '        .[A1].Select
  8.         Debug.Print .UsedRange.Address
  9.         On Error Resume Next
  10. '        .Cells.Ungroup
  11. '        .Cells.EntireColumn.Hidden = False
  12.         If ActiveWindow.FreezePanes Then ActiveWindow.FreezePanes = False
  13.         
  14. '        Columns("A:Z").Delete Shift:=xlToLeft
  15.          Col = .UsedRange.Column
  16.         C = .UsedRange.Columns.Count
  17.         .Cells(1, Col).Resize(.Rows.Count, C).Delete Shift:=xlToLeft
  18.          .[A1].Select
  19.         Debug.Print .UsedRange.Address
  20.               
  21.         On Error GoTo 0
  22.     End With
  23. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD