20180519 - VBA：在excel中实现不同sheet之间的数据重组

1. 选择待操作的工作表，建立sheet对应的变量

定义一个Worksheet工作表对象WS1:

Dim WS1 As Worksheet

把sheet“名单”赋值给指定的变量WS1:

Set WS1 ＝ Worksheets("名单")

或者，通过工作表出现在工作薄中的顺序从左到右，依次用1、2、3.……来引用（“名单”为sheet1）:

Set WS1 ＝ Worksheets(1)

2. 选择待操作的表格区域

单元格区域即为range对象，既可以是单个单元格，也可以是多个连续的单元格和多个不连续的单元格。

定义一个range对象rng1：

Dim rng1 As Range

3. 操作单元格对象

引用格式：

worksheet_object.Cells(row,col)

4. 查找Find()

5. 完整代码

Sub Recompose01()

    Dim SUM As Worksheet  'Defination of Two Sheets
    Dim AllOrgs As Worksheet
    Set SUM = Worksheets(1)
    Set AllOrgs = Worksheets(2)

    Dim A As Integer      '3 Column: ORG, ADD & Person
    ORG = 1
    Dim N As Integer
    Add = 14
    Dim Q As Integer
    Person = 17

    Dim SearchString As String
    Dim SearchRange As Range
    Dim ReturnRange As Range
    Dim ReturnRow As Integer
    Set SearchRange = AllOrgs.Range("A183:B427")
    For Ai = 3 To 5
        Debug.Print Ai
        Debug.Print SUM.Cells(Ai, ORG)
        SearchString = SUM.Cells(Ai, ORG) 'No Set
        Set ReturnRange = AllOrgs.Cells.Find(What:=SearchString)
        If Not ReturnRange Is Nothing Then
            ReturnRow = ReturnRange.Row
            Debug.Print ReturnRange
       
            Debug.Print ReturnRow
            SUM.Cells(Ai, Add) = AllOrgs.Cells(ReturnRow, 9) + AllOrgs.Cells(ReturnRow, 10) + AllOrgs.Cells(ReturnRow, 11)
            SUM.Cells(Ai, Person) = AllOrgs.Cells(ReturnRow, 4) + "（" + AllOrgs.Cells(ReturnRow, 6) + "）" + Str(AllOrgs.Cells(ReturnRow, 8))
            SUM.Cells(Ai, Person) = Trim(SUM.Cells(Ai, Person)) 'Delete SPACE
         End If
    Next

End Sub