|
代码如下- Option Explicit
- Sub 保存()
- Dim rng As Range, Str As String, UnRng As Range
- Set rng = Worksheets(3).Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
- Rem 检测必录信息是否录入
- With Worksheets(1)
- If .Range("c7") = "" Then MsgBox "请输入姓名": Exit Sub
- If .Range("e7") = "" Then MsgBox "请输入性别": Exit Sub
- If .Range("g7") = "" Then MsgBox "请输入出生年月": Exit Sub
- If .Range("g7") = "" Then MsgBox "请输入身份证": Exit Sub
- If .Range("c8") = "" Then MsgBox "请输入父亲姓名": Exit Sub
- If .Range("e8") = "" Then MsgBox "请输入父亲电话": Exit Sub
- If .Range("c9") = "" Then MsgBox "请输入母亲姓名": Exit Sub
- If .Range("e8") = "" Then MsgBox "请输入母亲电话": Exit Sub
- If .Range("i8") = "" Then MsgBox "请输入毕业院校": Exit Sub
- If .Range("i9") = "" Then MsgBox "请输入毕业班级": Exit Sub
- If .Range("c11") = "" Then MsgBox "请输入户籍所在地": Exit Sub
- If .Range("i9") = "" Then MsgBox "请输入家庭住址": Exit Sub
- End With
- Rem 将信息表数据引入date表
- With rng
- .Offset(0, -2) = Worksheets(1).Range("i8") '毕业院校
- .Offset(0, -1) = Worksheets(1).Range("i9") '毕业班级
- .Offset(0, 0) = Worksheets(1).Range("c7") '学生姓名
- .Offset(0, 1) = Worksheets(1).Range("e7") '性别
- .Offset(0, 2) = Worksheets(1).Range("g7") '出生日期
- .Offset(0, 3) = Worksheets(1).Range("i7") '身份证
- .Offset(0, 4) = Worksheets(1).Range("c8") '父亲姓名
- .Offset(0, 5) = Worksheets(1).Range("e8") '联系电话
- .Offset(0, 6) = Worksheets(1).Range("c9") '母亲姓名
- .Offset(0, 7) = Worksheets(1).Range("e9") '联系电话
- .Offset(0, 8) = Worksheets(1).Range("i11") '类型
- .Offset(0, 9) = Worksheets(1).Range("c11") '户籍所在
- .Offset(0, 10) = Worksheets(1).Range("c12") '家庭住址
- .Offset(0, 11) = Worksheets(1).Range("c14") '父亲身份
- .Offset(0, 12) = Worksheets(1).Range("e14") '母亲身份
- .Offset(0, 13) = Worksheets(1).Range("g14") '房本
- .Offset(0, 14) = Worksheets(1).Range("i14") '务工
- .Offset(0, 15) = Worksheets(1).Range("d16") '报到日期
- End With
- End Sub
- Sub 清除() '清除信息表残留数据
- Dim UnRng As Range
- With Worksheets(1)
- Set UnRng = Union(.Range("c7"), .Range("e7"), .Range("g7"), .Range("i7"), _
- .Range("c8"), .Range("e8:g8"), .Range("i8"), .Range("c9"), .Range("e9:G9"), _
- .Range("i9"), .Range("c11:G11"), .Range("c12:g12"), .Range("i11:i12"), .Range("c14"), _
- .Range("e14"), .Range("g14"), .Range("i14")) '将需要清除数据的单元格连接起来
- UnRng.ClearContents '仅清除UnRng中的值,保留格式
- End With
- End Sub
复制代码
|
|