Solved: VBA Program to Compare 4 Columns in Excel (Required)

[vijaysram]

Hi all

I am New to VBA programming in Excel. Can someone please help me how to create a VBA Program to Compare 4 Columns in Excel and store the values in another column. I have searched it in multiple websites but i couldn't find it. I have got a VBA to compare 2 columns , please let me know how to create it for 4 columns

[VBA]Private Sub CommandButton1_Click()
Dim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant
str1 = InputBox("Enter Column Name to be Compared")
str2 = InputBox("Enter Column Name to Compare")
str3 = InputBox("Enter Column Name to put the Result")
Range(str1 & "1").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range(str1 & "1:" & Selection.Address)
Range(str2 & "1").Select
Selection.End(xlDown).Select
Set CompareRange = Range(str2 & "1:" & Selection.Address)
i = 1
To_Be_Compared.Select
For Each x In Selection
For Each y In CompareRange
If x = y Then
Range(str3 & i).Value = x
i = i + 1
End If
Next y
Next x
End Sub[/VBA]

[SamT]

[vba]Private Sub CommandButton1_Click()
Dim CompareRange As Range, To_Be_Compared As Range, x As Range, y As Range
Dim str1 As String, str2 As String, str3 As String, str4 As String
Dim LR As Long, rw As Long

str1 = InputBox("Enter Column Name to be Compared")
str2 = InputBox("Enter First Column Name to Compare")
str3 = InputBox("Enter Last Column Name to Compare")
str4 = InputBox("Enter Column Name to put the Result")

LR = Range(str1 & "1").End(xlDown).Row
Set To_Be_Compared = Range(str1 & "1:" & str1 & LR)

If str3 = "" Or str3 = str2 Then
Set CompareRange = Range(str2 & "1:" & str2 & LR)
Else
Set CompareRange = Range(str2 & "1:" & str3 & LR)
End If

rw = 1
For Each x In To_Be_Compared
Set y = CompareRange.Find(x)
If Not y Is Nothing Then
Range(str4 & rw) = x
rw = rw + 1
End If
Next x
End Sub
[/vba]

[vijaysram]

Thank you so much for your kind assistance..It helps me to save a lot of time :-)