去其他表里查找数据，并更新原表数据

 已有数据

想在课程名后加上“回车教师1+教师2+教师3 ”

 

Sub getdata()
    Dim tmp As String
    Dim tmpname As String
    Dim x%, y%, i%, flag%, ishav%
    n = 2 '新表数据起点行数
    r = 41 '原始数据右边界
    Dim row%
    flag = 0
    ishav = 0
    i = 2
    row = 14 '原表数据行数
     For y = 2 To r
        flag = 0
        ishav = 0
        tmpname = ""
        tmp = Sheet1.Cells(row, y)
        For i = 2 To 160
            If Sheet2.Cells(i, 1) = "高三年级" And Sheet2.Cells(i, 2) = tmp Then
                If flag = 0 Then
                 tmpname = tmpname & Sheet2.Cells(i, 3)
                 flag = 1
                 ishav = 1
                Else
                     tmpname = tmpname & "+" & Sheet2.Cells(i, 3)
                 End If
            End If
         Next
         If ishav = 1 Then
            Sheet1.Cells(row, y) = Sheet1.Cells(row, y) & Chr(10) & tmpname
         End If
    Next
End Sub