PDA

View Full Version : Need to Compare any Columns as given by user



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

Ischyros
11-27-2008, 10:14 AM
The code below takes a column selection from the user and then finds the column index number by scanning the column addresses one at a time, starting with column A, until the column address matches what the user selected colum. This can be repeated for column2 as well. I was a bit confused by your question....Do you also want to code for comparing values, or did you just need code to find the column index number?

Sub column_no()

Dim column1 As Range, searchcolumn As Range

Set column1 = Application.InputBox("Select First Column to Compare", Type:=8)

Set searchcolumn = Worksheets(1).Columns(1)

Dim i As Integer
i = 1

While searchcolumn.Address <> column1.Address
i = i + 1
Set searchcolumn = Worksheets(1).Columns(i)
Wend

End Sub

"i" is then the column index number....hope this help! Let me know if this is what you needed.

mikerickson
11-27-2008, 10:32 AM
The .Column property (e.g. Column1.Column) will return the number of the user selected column.

staycool
11-30-2008, 08:55 PM
I want to compare two column values.Thanks a lot for the replies.