Try this
Code:Private Sub CommandButton1_Click() Dim lngColumnIndex() As Long Dim lngLoop As Long, lngSelected As Long, lngRows As Long Dim strColumnHeaders As String, strSelected As String Dim blnHoldsTrue As Boolean Const lngColumnHeaderRow As Long = 1 Const lngColumnsToCompare As Long = 4 On Error GoTo Err ReDim lngColumnIndex(1 To lngColumnsToCompare + 1) For lngLoop = 1 To ActiveSheet.UsedRange.Columns.Count strColumnHeaders = strColumnHeaders & lngLoop & " - " & Cells(lngColumnHeaderRow, lngLoop).Value & "|" Next lngLoop strColumnHeaders = "The column headers are " & vbLf & vbLf & Join(Split(strColumnHeaders, "|"), vbLf) & vbLf For lngLoop = 1 To lngColumnsToCompare For lngSelected = 1 To lngLoop - 1 strSelected = strSelected & lngColumnIndex(lngSelected) & vbLf Next lngSelected lngColumnIndex(lngLoop) = InputBox(strColumnHeaders & strSelected & "Enter each column index one by one", "Column Compare") strSelected = "You have already selected:" & vbLf & vbLf Next lngLoop For lngSelected = 1 To lngLoop - 1 strSelected = strSelected & lngColumnIndex(lngSelected) & vbLf Next lngSelected lngColumnIndex(lngLoop) = InputBox(strColumnHeaders & strSelected & "Enter column index where you want to show the comparison result", "Column Compare") lngRows = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row blnHoldsTrue = True For lngRows = lngColumnHeaderRow + 1 To lngRows For lngLoop = 2 To lngColumnsToCompare blnHoldsTrue = blnHoldsTrue And (Cells(lngRows, lngColumnIndex(lngLoop)).Value = Cells(lngRows, lngColumnIndex(lngLoop - 1)).Value) Next lngLoop Cells(lngRows, lngColumnIndex(lngLoop)).Value = blnHoldsTrue blnHoldsTrue = True Next lngRows Exit Sub Err: MsgBox "Either cancelled by user, or incorrect entry made." & vbLf & vbLf & "If neither of these, unexpected error!", vbOKOnly + vbInformation, "" End Sub




Reply With Quote

Bookmarks