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