VBA Excel 对比两列数据

时间：2024-02-15 11:45:07

 使用 C# 写的数字转字母

private string ToColumn(int p)
{
    if (p > 26)
    {
        char A = (char)(\'A\' + (p - 1) / 26 - 1);
        char B = (char)(\'A\' + (p - 1) % 26);
        return new string(new char[] { A, B });
    }
    else
        return ((char)(\'A\' + (p - 1))).ToString();
}

 

Sub Md()
\'
\' Macro1 Macro
\' 宏由 BX 录制，时间: 2012-6-8
\' 宏中的列数可以输入 A - IV 也可以输入 1-256
\'
Dim i%, j%, i1%, j1%, i2%, j2%
myi = UCase(InputBox("第一列"))
myj = UCase(InputBox("第二列"))
\'
\'************************************

If myi Like "[A-Z]" Then
i = Asc(myi) - 64
ElseIf myi Like "[A-I][A-Z]" Then
i = (Asc(Left(myi, 1)) - 64) * 26 + Asc(Right(myi, 1)) - 64
ElseIf IsNumeric(myi) Then
i = myi
Else
i = 1
End If
If myj Like "[A-Z]" Then
j = Asc(myj) - 64
ElseIf myj Like "[A-I][A-Z]" Then
j = (Asc(Left(myj, 1)) - 64) * 26 + Asc(Right(myj, 1)) - 64
ElseIf IsNumeric(myj) Then
j = myj
Else
j = 2
End If

\'ucase 转大写 upper lcase 转小写 lower

i2 = 40      \'i列颜色
j2 = 40      \'j列颜色

\'在上面更改哪两列对比,对比相同后的背景色
\'************************************
\'以下内容不用修改
Application.ScreenUpdating = False
If i = j Then j = i + 1
For i1 = 1 To Cells(30000, i).End(3).Row
For j1 = 1 To Cells(30000, j).End(3).Row
If Cells(i1, i).Value = "" Then Exit For
If Cells(j1, j).Interior.ColorIndex <> 40 And Cells(i1, i).Value = Cells(j1, j).Value Then
Cells(i1, i).Interior.ColorIndex = i2
Cells(j1, j).Interior.ColorIndex = j2
Exit For
End If
Next
Next
Application.ScreenUpdating = True
MsgBox "对比完成！" & vbCrLf & "刚才对比的是 " & i & " 列和 " & j & " 列的数据"
\' 如果不想弹出提示 把上面前面加上 \' 英文单引号即可
End Sub

 

相关文章