|
楼主 |
发表于 2019-2-16 14:56
|
显示全部楼层
想到个曲线救国的方法,先在表两个固定名字的表,然后把动态名字的表复制过去,处理完再覆盖回去
虽然复杂了点,但成功了额
有大佬能教我简单点的方法吗
Dim a, b, j, k
Sheet1.Columns(3).EntireColumn.Insert
Sheet2.Columns(3).EntireColumn.Insert
Sheet1.Select
Range("c2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'2222'!C[-1]:C,1,FALSE),""0"")"
a = Cells(Cells.Rows.Count, 1).End(xlUp).Row
Selection.AutoFill Destination:=Range("c2:c" & a), Type:=xlFillDefault
Sheet2.Select
Range("c2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'1111'!C[-1]:C,1,FALSE),""0"")"
b = Cells(Cells.Rows.Count, 1).End(xlUp).Row
Selection.AutoFill Destination:=Range("c2:c" & b), Type:=xlFillDefault
Sheet1.Select
j = 2
Do While Cells(j, 3) <> ""
If Cells(j, 3) = 0 Then
Cells(j, 3).Font.Color = vbRed
Cells(j, 3).EntireRow.Font.Color = vbRed
End If
j = j + 1
Loop
Range("c:c").Delete
Sheet2.Select
k = 2
Do While Cells(k, 3) <> ""
If Cells(k, 3) = 0 Then
Cells(k, 3).Font.Color = vbRed
Cells(k, 3).EntireRow.Font.Color = vbRed
End If
k = k + 1
Loop
Range("c:c").Delete
Worksheets(1).Activate
ActiveCell.CurrentRegion.Select
Selection.ClearContents
Worksheets(2).Activate
ActiveCell.CurrentRegion.Select
Selection.ClearContents
Sheet1.Activate
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets(1).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheet2.Activate
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets(2).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheet1.Delete
Sheet2.Delete
End Sub |
|