EXCEL数据对比的完整版  

2009-02-27 14:28:12|  分类： 默认分类 |举报 |字号 订阅

Sub Macro1()
'这是EXCEL中的宏,用VB的脚本.把它放到EXCEL中的宏里面就可能了

'仍旧有缺限，在表一中的数据表二没有的情况下出报错，不知道如何解决．如果有高手的话可能看着给改一下.在这里先谢谢了.

'需要此宏的朋友请保留以上的字符
' Macro4 Macro
' 宏由 老杨 录制，时间: 2007-11-20
'
Dim nrow1, nrow2, nnum1, nnum2, nnum, loopp1, bbc, cctvv, eeeof, neeeof, ninput, ploopp
'

bbc = 1
nrow2 = 1
loopp1 = 1
nnum = "A1"
eeeof = "1"

Sheets("Sheet1").Select
Range("A1").Select

ninput = Application.InputBox("是不需要将对比结果输入到第三表中，输入Y，确定，其它不需要", "告诉老杨")
ploopp = Application.InputBox("请输入要处理的行数", "请输入数字", Number)
ploopp = CInt(ploopp)
Do While loopp1 < ploopp

cctvv = CStr(ActiveCell)
    Sheets("Sheet2").Select
    Range("a1").Select
    nrow1 = "1"
    Cells.Select
   
    Selection.Find(What:=cctvv, After:=ActiveCell, SearchDirection:=xlNext, _
         MatchByte:=False).Activate
       
nrow1 = CStr(ActiveCell.Cells.Row())
If eeeof = nrow1 Then
Sheets("sheet1").Select
Rows(nnum2).Select
 With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With

Else

nnum1 = CStr(nrow1)
nnum1 = nnum1 + ":" + nnum1
nnum2 = CStr(nrow2)
nrow2 = nrow2 + 1
nnum2 = "A" + nnum2
'在二里面加颜色
Rows(nnum1).Select
 With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    End If
   
'*****************************************
Application.CutCopyMode = False
If ninput = "Y" Then
  Sheets("sheet3").Select
  Range(nnum).Select
  ActiveCell.FormulaR1C1 = "电话" + cctvv + "在表二的第" + nrow1 + "行"
  End If
       
    Sheets("Sheet1").Select
  
    nnum = CStr(nrow2)
    nnum = "A" + nnum
    Range(nnum).Select
       
   
   
    loopp1 = loopp1 + 1

   
  Loop
 
End Sub