麻辣家族討論版版 - Powered by Discuz! Board

Sub 按鈕9_Click()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
'What are the names of our worksheets?
Set sourceSheet = Worksheets("competencylist_2016")
Set outputSheet = Worksheets("inserttext")
'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
'Determine last row in col P
OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
'Apply our formula
.Range("B6:B20" & OutputLastRow).Formula = _
"=INDEX('competencylist_2016'!$C$2:$C$100, SMALL(INDEX(($H$2='competencylist_2016'!$A$2:$A$100)*(MATCH(ROW('competencylist_2016'!$A$2:$A$100),ROW('competencylist_2016'!$A$2:$A$100)))+($H$2<>'competencylist_2016'!$A$2:$A$100)*1048577,0,0),ROW('competencylist_2016'!A1)))"
.Range("C6:C20" & OutputLastRow).Formula = _
"=VLOOKUP(B:B,'" & sourceSheet.Name & "'!$C$1:$F$1" & SourceLastRow & ",2,0)"
End With
End Sub

複製代碼

Range("B6:B20" & OutputLastRow).Formula
.Range("C6:C20" & OutputLastRow).Formula =

複製代碼

Option Explicit
Sub 按鈕9_Click()
Dim sourceSheet As Worksheet '** 變數型態為工作表物件
Dim outputSheet As Worksheet '** 變數型態為工作表物件
'What are the names of our worksheets?
Set sourceSheet = Worksheets("competencylist_2016")
Set outputSheet = Worksheets("inserttext")
outputSheet.Range("B6:I" & Rows.Count) = "" '**清除舊有的資料
With sourceSheet
'**Count 屬性傳回一個 Long (長整數)，等於在一個集合物件中的物件數目。唯讀。
'** Columns.Count -> 工作表的所有欄數
.Cells(1, Columns.Count - 3).Resize(, 4) = Array(.Range("A1"), "", .Range("C1"), .Range("D1"))
'** 寫入字串 BBItem1 ,"", BBItem3, BBItem4
.Cells(2, Columns.Count - 3) = outputSheet.Range("H2") '**職務編號
'**進階篩選
.Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, .Cells(1, Columns.Count - 3).Resize(2), .Cells(1, Columns.Count - 1).Resize(1, 2)
'.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Cells(1, Columns.Count - 3).Resize(2), CopyToRange:=.Cells(1, Columns.Count - 1).Resize(1, 2)
'** AdvancedFilter 範圍的進階篩選 , xlFilterCopy 篩選到其他位置
'**AdvancedFilter 方法基於準則範圍從資料清單中篩選或複製資料。如果初始選定為單個儲存格，則使用儲存格目前的區域x為Variant。
'** expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
'**expression 必選。該運算式會傳回 [套用於] 清單中的其中一個物件。
'** Action 必選的 XlFilterAction 資料類型。
'** XlFilterAction 可以是這些 XlFilterAction 常數之一。 xlFilterCopyxlFilterInPlace
'**CriteriaRange 選擇性的 Variant。準則範圍。如果省略此引數則無準則。
'**CopyToRange 選擇性的 Variant。如果 Action 為 xlFilterCopy，此引數指定被複製列的目標範圍。否則忽略此引數。
'**Unique 選擇性的 Variant。若為 True，則僅篩選唯一的記錄；若為 False，則篩選出所有符合準則的記錄。預設值為 False。
With .Cells(1, Columns.Count - 1).Resize(1, 2).CurrentRegion
If .Rows.Count > 1 Then
outputSheet.Range("B6").Resize(.Rows.Count - 1, 2) = .Rows("2:" & .Rows.Count).Value
With outputSheet.Range("H6").Resize(.Rows.Count - 1, 2)
.Columns(1) = "=RC[-2]-RC[-3]" '公式 : 用 R1C1相對位置
.Columns(2) = "=RC[-2]-RC[-4]"
End With
End If
End With
.Cells(1, Columns.Count - 3).Resize(, 4).EntireColumn = "" '範圍: 清除字串
End With
End Sub

複製代碼