两个excel工作簿的操作
发布网友
发布时间：2022-04-23 06:59
共3个回答
热心网友
时间：2022-06-17 03:56
你把这两个表发给我邮箱 5529072@qq.com
我在教务处工作能搞定 写vba
这是大概的思路
Sub St()
Dim bj As String '班级表名称
Dim rng As Range
With Application.Workbooks("book2").Sheets("a+b班级男生")
For i = ht To hw 'a+b班级男生表的行数
Dim id As String
id = .Cells(i, 1).Value
Set rng = Application.Workbooks("book1").Sheets(1).Range("A:A").Find(id, , , 1)
If Not rng Is Nothing Then
Dim hh As Integer
hh = rng.Row
Application.Workbooks("book1").Sheets(1).Cells(hh, 5).Value = .Cells(i, 2).Value
End If
Set rng = Application.Workbooks("book1").Sheets(2).Range("A:A").Find(id, , , 1)
If Not rng Is Nothing Then
Dim hh As Integer
hh = rng.Row
Application.Workbooks("book1").Sheets(2).Cells(hh, 5).Value = .Cells(i, 2).Value
End If
Next i
End Sub
热心网友
时间：2022-06-17 03:57
book2.xls中的学号在A列，寝室号在D列：
Private Sub CommandButton1_Click()
i = 2
pa = ThisWorkbook.Path
Do Until Range("A" & i) = ""
If Dir(pa & "\" & "book2.xls") = "" Then
Range("c" & i) = "无book2.xls"
Else
Range("D" & i).Formula = "='" & pa & "\[" & "book2]Sheet1'!" & "D" & i
End If
i = i + 1
Loop
End Sub追问book1
sheet1(a班）
学号，电话，专业
***** **** *****
sheet2(b班）
学号，电话，专业
***** **** *****
book2
sheet1(a+b班男）
学号，寝室
***** ****
sheet2(a+b班女）
学号，寝室
***** ****
现需要
sheet1(a班）
学号，电话，专业，寝室
***** **** ***** ****
sheet2(b班）
学号，电话，专业，寝室
***** **** ***** ****
谢谢！
追答直接用公式就行，先将book2中sheet2内容复制到sheet1。
然后在book1中，D2用公式：=VLOOKUP(A2,[book2.xls]Sheet1!$A$2:$B$200,2,0).
如果要求用VBA，就是：
Private Sub CommandButton1_Click()
i = 2
pa = ThisWorkbook.Path
If Dir(pa & "\" & "book2.xls") = "" Then
Range("c" & i) = "无book2.xls"
Else
Do Until Range("A" & i) = ""
z = 1
j = 2
Do Until z = 0
Range("Y2").Formula = "='" & pa & "\[" & "book2]Sheet1'!" & "A" & j
z = Range("Y2")
If z = Range("A" & i) Then
Range("D" & i).Formula = "='" & pa & "\[" & "book2]Sheet1'!" & "B" & j
z = 0
Else
j = j + 1
End If
Loop
z = 1
j = 2
Do Until z = 0
Range("Y2").Formula = "='" & pa & "\[" & "book2]Sheet2'!" & "A" & j
z = Range("Y2")
If z = Range("A" & i) Then
Range("D" & i).Formula = "='" & pa & "\[" & "book2]Sheet2'!" & "B" & j
z = 0
Else
j = j + 1
End If
Loop
i = i + 1
Loop
End If
Range("Y" & i) = ""
End Sub
热心网友
时间：2022-06-17 03:57
用VLOOKUP轻松搞定，用宏显得很麻烦。
