staycool
11-26-2008, 11:05 PM
Hi All,
I have an Excel Sheet with specific with some entries in 5 Columns .I have to write a code to compare two colums selected by user.I have already written a code to select 2 columns clicked by the user. the Code i have wriiten is given below.I am struck up,as i the code is not comparing for the values selcted by user.It compares the values in Column1 and Column2.So i need to convert get the "column no" of the user selected Columns.For ed A as 1,B as 2, C as 3 etc..Please Help me.
Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim one As Integer
Dim two As Integer
Dim Sht1Val As String
Dim Sht2Val As String
Dim x, y As Integer
Dim Column1 As Range
Dim Column2 As Range
'Prompt user for the first column range to compare...
'----------------------------------------------------
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)
'Check that the range they have provided consists of only 1 column...
If Column1.Columns.Count > 1 Then
Do Until Column1.Columns.Count = 1
MsgBox "You can only select 1 column"
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)
Loop
End If
'Prompt user for the second column range to compare...
'----------------------------------------------------
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
'Check that the range they have provided consists of only 1 column...
If Column2.Columns.Count > 1 Then
Do Until Column2.Columns.Count = 1
MsgBox "You can only select 1 column"
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
Loop
End If
'Check both column ranges are the same size...
'---------------------------------------------
If Column2.Rows.Count <> Column1.Rows.Count Then
Do Until Column2.Rows.Count = Column1.Rows.Count
MsgBox "The second column must be the same size as the first"
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
Loop
End If
'If entire columns have been selected (e.g. $AA), limit the range sizes to the
'UsedRange of the active sheet. This stops the routine checking the entire sheet
'unnecessarily.
'-------------------------------------------------------------------------------
If Column1.Rows.Count = 65536 Then
Set Column1 = Range(Column1.Cells(1), Column1.Cells(ActiveSheet.UsedRange.Rows.Count))
Set Column2 = Range(Column2.Cells(1), Column2.Cells(ActiveSheet.UsedRange.Rows.Count))
End If
LastRow1 = Column1.Rows.Count
LastRow2 = Column2.Rows.Count
For one = 1 To LastRow1
Sht1Val = Sheets("Sheet1").Cells(one, 1).Value
'start at 2 if you have column headers if not, then start at 1
For two = 1 To LastRow2
Sht2Val = Sheets("Sheet1").Cells(two, 2).Value
If Sht2Val Like Sht1Val Then
Sheets("Sheet1").Select
Range(Cells(two, 1), Cells(two, 2)).Select
Sheets("Sheet1").Cells(two, 2).Value = ""
'Application.CutCopyMode = False
'Selection.Delete Shift:=xlUp
End If
Next two
Next one
I have an Excel Sheet with specific with some entries in 5 Columns .I have to write a code to compare two colums selected by user.I have already written a code to select 2 columns clicked by the user. the Code i have wriiten is given below.I am struck up,as i the code is not comparing for the values selcted by user.It compares the values in Column1 and Column2.So i need to convert get the "column no" of the user selected Columns.For ed A as 1,B as 2, C as 3 etc..Please Help me.
Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim one As Integer
Dim two As Integer
Dim Sht1Val As String
Dim Sht2Val As String
Dim x, y As Integer
Dim Column1 As Range
Dim Column2 As Range
'Prompt user for the first column range to compare...
'----------------------------------------------------
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)
'Check that the range they have provided consists of only 1 column...
If Column1.Columns.Count > 1 Then
Do Until Column1.Columns.Count = 1
MsgBox "You can only select 1 column"
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)
Loop
End If
'Prompt user for the second column range to compare...
'----------------------------------------------------
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
'Check that the range they have provided consists of only 1 column...
If Column2.Columns.Count > 1 Then
Do Until Column2.Columns.Count = 1
MsgBox "You can only select 1 column"
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
Loop
End If
'Check both column ranges are the same size...
'---------------------------------------------
If Column2.Rows.Count <> Column1.Rows.Count Then
Do Until Column2.Rows.Count = Column1.Rows.Count
MsgBox "The second column must be the same size as the first"
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
Loop
End If
'If entire columns have been selected (e.g. $AA), limit the range sizes to the
'UsedRange of the active sheet. This stops the routine checking the entire sheet
'unnecessarily.
'-------------------------------------------------------------------------------
If Column1.Rows.Count = 65536 Then
Set Column1 = Range(Column1.Cells(1), Column1.Cells(ActiveSheet.UsedRange.Rows.Count))
Set Column2 = Range(Column2.Cells(1), Column2.Cells(ActiveSheet.UsedRange.Rows.Count))
End If
LastRow1 = Column1.Rows.Count
LastRow2 = Column2.Rows.Count
For one = 1 To LastRow1
Sht1Val = Sheets("Sheet1").Cells(one, 1).Value
'start at 2 if you have column headers if not, then start at 1
For two = 1 To LastRow2
Sht2Val = Sheets("Sheet1").Cells(two, 2).Value
If Sht2Val Like Sht1Val Then
Sheets("Sheet1").Select
Range(Cells(two, 1), Cells(two, 2)).Select
Sheets("Sheet1").Cells(two, 2).Value = ""
'Application.CutCopyMode = False
'Selection.Delete Shift:=xlUp
End If
Next two
Next one