[color=gray][size=large]1.在某个Sheet的某列前面插入新的行[/size][/color]
[color=gray][size=large]2.对指定的列进行判断是否需要复制,并复制特定内容[/size][/color]
[color=gray][size=large]3.将根据按照一定的规则回填[/size][/color]
'在某个Sheet的某列前面插入新的行
'sheetName sheet名称
'rowKey 行标志,eg: A B C
Sub insertNewRow(sheetName As String, rowKey As String)
Dim col As String
col = rowKey & ":" & rowKey
Sheets(sheetName).Columns(col).Insert
End Sub
[color=gray][size=large]2.对指定的列进行判断是否需要复制,并复制特定内容[/size][/color]
' 将Sheet1中A列不为空的行 A列 C列的值复制到
' Sheet2中的 A列 B列
' Sheet1中 A列的值对应 Sheet2中的B列
' Sheet1中 C列的值对应 Sheet2中的A列
Sub copyCellToTempSheet()
' 定义复制的行,黏贴的行
Dim copyCount, patseCount As Integer
'需要进行判断的单元格
Dim copyCell As Range
' 初始化数据,判断复制的行数与黏贴的行数(黏贴的行数1还是2 根据是否有标题进行选择)
' 复制的行数以A使用的行数
copyCount = Sheets("Sheet1").Range("A65535").End(xlUp).Row
patseCount = 1
'遍历所数据
For i = 2 To copyCount
'获取Sheet1中A列值,判断是否为空
Set copyCell = Sheets("Sheet1").Cells(i, "A")
If copyCell.Value <> "" Then
'不为空则进行赋值操作,注意对应关系
Sheets("Sheet2").Cells(patseCount, "A").Value = Sheets("Sheet1").Cells(i, "C").Value
Sheets("Sheet2").Cells(patseCount, "B").Value = Sheets("Sheet1").Cells(i, "A").Value
patseCount = patseCount + 1
End If
Next
End Sub
[color=gray][size=large]3.将根据按照一定的规则回填[/size][/color]
' 将Sheet2中的临时数据根据对应关系还原到目标Sheet1中
' Sheet1中的C列 对应 Sheet2中的A列
' 将Sheet2中的B列的值存放在Sheet1对应的B列
Sub patseCellToSourceSheet()
Dim copyCount, patseCount As Integer
'初始化数据,判断复制的行数与黏贴的行数
copyCount = Sheets("Sheet2").Range("A65535").End(xlUp).Row
patseCount = Sheets("Sheet1").Range("C65535").End(xlUp).Row
'需要进行判断的单元格
Dim copyCell, patseCell As Range
'遍历所有数据进行匹配
For i = 1 To copyCount
Set copyCell = Sheets("Sheet2").Cells(i, "A")
For j = 2 To patseCount
Set paseCell = Sheets("Sheet1").Cells(j, "C")
' 匹配成功,则进行反向赋值
If copyCell.Value = paseCell.Value Then
Sheets("Sheet1").Cells(j, "B").Value = Sheets("Sheet2").Cells(i, "B").Value
j = patseCount
End If
Next
Next
End Sub