VBA

学了两天的VBA 终于自己写了个小程序，excel里面用VBA来处理还真是方便快捷。

//这个程序的主要作用是，自动筛选透视图中所用值，每次筛选之后，自动复制这些值到另一个sheet。

Sub test()

Dim i As Integer
Dim j As Integer
Dim Max As Integer

Dim Dealercode As String
Dim Date_number As String

For i = 1 To 150//第一个for循环用于筛选透视图中的值，选择150 个不同的dealer_code

Dealercode = Sheets("Dealer_Data").Range("a" & i).Text

Sheets("Data_Source").Select

Sheets("Data_Source").PivotTables(1).PivotFields("Dealer_Code"). _
ClearAllFilters
Sheets("Data_Source").PivotTables(1).PivotFields("Dealer_Code").CurrentPage _
= Dealercode
Sheets("Dealer_Data").Select
Sheets("Dealer_Data").Cells(2, 5 + i).FormulaR1C1 = Dealercode

For j = 0 To 116//第二for循环用于将每个dealer_code中的116个值复制到另一个sheet。
Sheets("Data_Source").Select
Data_number = Sheets("Data_Source").Range("ab" & (7 + j)).Text
Sheets("Dealer_Data").Select
Sheets("Dealer_Data").Cells(3 + j, 5 + i).FormulaR1C1 = Data_number
Next j

Next i

End Sub

Sub Calculate_One_Sheet()
Dim rate As Double

ActiveSheet.Range("D4").Select
rate = ActiveSheet.Range("C4").Value
ActiveCell.FormulaR1C1 = "1"
Range("J3").Select
Range("J3").GoalSeek Goal:=rate, ChangingCell:=Range("D4")
Range("D4").Select

End Sub