用VBA解决比较Excel两列的值

比较Excel列A与列B的数据
1,列A有,列B没有
Private Sub CommandButton1_Click()
x = InputBox(“请输入末行号”)
   For i = 1 To x ‘假定首行为标题行,如果没有标题行,则将2改为1
       strA = Worksheets(“sheet1”).Cells(i, 1).Value ‘得到列1的数据
       For j = 1 To x ‘遍历列2
           strB = Worksheets(“Sheet1”).Cells(j, 2).Value ‘得到列2的数据
          
           If strB = strA Then ‘如果列1中有,而列2中也有,则跳过,比较下一个列1的数据
               GoTo a:
           End If
       Next j
       Worksheets(“sheet1”).Cells(i, 3).Value = Worksheets(“sheet1”).Cells(i, 1).Value ‘列1中的某在数据在列2中不存在,则写入列3
a:     Next i
End Sub

2,列B有,列A没有
Private Sub CommandButton2_Click()
x = InputBox(“请输入末行号”)
   For i = 1 To x ‘假定首行为标题行,如果没有标题行,则将2改为1
       strA = Worksheets(“sheet1”).Cells(i, 2).Value
       For j = 1 To x
           strB = Worksheets(“Sheet1”).Cells(j, 1).Value
          
           If strB = strA Then
               GoTo a:
           End If
       Next j
       Worksheets(“sheet1”).Cells(i, 3).Value = Worksheets(“sheet1”).Cells(i, 2).Value
a:     Next i
End Sub

3,列A,列B都有
Private Sub CommandButton3_Click()
   x = InputBox(“请输入末行号”)
   For i = 1 To x ‘假定首行为标题行,如果没有标题行,则将2改为1
       strA = Worksheets(“sheet1”).Cells(i, 1).Value
       For j = 1 To x
           strB = Worksheets(“Sheet1”).Cells(j, 2).Value
           If strB = strA Then
               Worksheets(“sheet1”).Cells(i, 3).Value = Worksheets(“sheet1”).Cells(i, 1).Value
               GoTo a:
           End If
       Next j
a:     Next i
End Sub

总结:一个朋友经常要处理一些名称,但经常有一大堆名称中有一小部分对不上的情况,于是找我帮助,刚开始,我都是导入到SQL Server处理,这样显然有点麻烦.虽然听说过用VBA可以解决,这样就可以告诉他,教他直接用VBA处理,但是从也没有写过VBA.今天,上网找了下信息后,感觉语法上跟VB一样,也不难.只是之前没有去了解而已.

 » 本站地址:http://www.gomoth.com

标签: