Comparing Columns VBA Assistance Required!

[HTMLGhozt]

Hello!
Excel, VBA 2010

I've been fiddling with this subroutine for a few days now and for a while it worked fine. I've finished the entire spreadsheet and this broke.... I can't seem to figure out what's wrong with it.

I hacked this from wellsr.com and reformatted it to what I need.

This is fairly simple it's comparing two columns of names making sure they're both the same and if they aren't depending on other variables it pastes a message in that row. Let's see if we can get this in working order please!

Sub CompareColumns_Test(ws2)


Dim MbrName_Console As String 'Member Names (Console)
Dim MbrName_Carrier As String 'Member Names (Carrier)
Dim DepName_Console_Check As String 'Dependent Names (Console)
Dim Console_NPortal As String 'In Console not Portal
Dim Portal_NConsole As String 'In Portal not Console
Dim iListStart As Integer 'Row where List Begins
Dim Console_NPortal_Msg As String, Portal_NConsole_Msg As String, ConsoleDep_NPortal_Msg As String 'Messages
Dim i As Integer, j As Integer
Dim iLastRow1 As Integer, iLastRow2 As Integer
Dim ConsoleDep_NPortal As String
Dim strtemp As String




'---Edit these variables---'
MbrName_Console = "S" 'Member Names (Console)
MbrName_Carrier = "Y" 'Member Names (Carrier)
DepName_Console_Check = "O" 'Dependent Names (Console)
Console_NPortal = "T" 'In Console not Portal
Portal_NConsole = "X" 'In Portal not Console
ConsoleDep_NPortal = "U" 'Dependent in Console not Portal
iListStart = 4 'Row start
'--------------------------'


iLastRow1 = ws2.Range(MbrName_Console & Rows.Count).End(xlUp).Row
iLastRow2 = ws2.Range(MbrName_Carrier & Rows.Count).End(xlUp).Row


With ws2
Range(Console_NPortal & iListStart & ":" & Portal_NConsole & (iLastRow1 + iLastRow2)).ClearContents


Console_NPortal_Msg = "In Console Not in Portal"
ConsoleDep_NPortal_Msg = "Dependent in Console not Portal"
Portal_NConsole_Msg = "In Portal Not in Console"


'Identify in Console not Portal
For i = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2)
  For j = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2)
    If (Range(MbrName_Carrier & i)) = (Range(MbrName_Console & j)) Then
      Exit For
    ElseIf j = WorksheetFunction.Min(iLastRow1, iLastRow2) Then
        If Not IsEmpty(Range(DepName_Console_Check & i)) Then
      Range(Console_NPortal & i) = Console_NPortal_Msg
        Else
        Range(ConsoleDep_NPortal & i) = ConsoleDep_NPortal_Msg
    End If
    End If
  Next j
Next i


'Identify in Portal not Console
End If
For i = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2)
  For j = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2)
    If (Range(MbrName_Console & j)) = (Range(MbrName_Carrier & i)) Then
      Exit For
    ElseIf j = WorksheetFunction.Max(iLastRow1, iLastRow2) Then
      Range(Portal_NConsole & i) = Portal_NConsole_Msg
    End If
  Next j
Next i
End With
End Sub

 

[HTMLGhozt]

Bump!

 

[HTMLGhozt]

Bump!

 

[wellsr]

I'm happy to see you based this off my article Where is your code failing? I'll see what I can do to help

 

[HTMLGhozt]

Hello Wellsr,

If I remember correctly the module I lobotomized from your code either wouldn't compare the second column or wouldn't compare the second column correctly, pasting erroneous values into cells.

I went a different direction using two .range.find methods and two loops to provide the same effect. I would still love to hear what your thoughts are on this.

Dim ws2 as worksheet
Dim Col1 As Range, Col2 As Range
Dim i as integer, j as integer
Dim iLastRow1 As Long, iLastRow2 As Long

set ws2 = thisworkbook.worksheets("Sheet1")

    iLastRow1 = ws2.Range("A" & Rows.Count).End(xlUp).Row
    iLastRow2 = ws2.Range("B" & Rows.Count).End(xlUp).Row

For i = 1 To iLastRow1
        Set Col1 = ws2.Range("B1:B" & iLastRow2).Find(What:=ws2.Range("A" & i).Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Col1 Is Nothing Then
           .Range("A" & i) = .range("A" & i).value & "is in column A, but not B."
        End If
    Next i
    
    For j = 1 To iLastRow2
        Set Col2 = ws2.Range("A1:A" & iLastRow1).Find(What:=ws2.Range("B" & j).Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Col2 Is Nothing Then
            ws2.Range("B" & j) = ws2.range("B" & j).value & "is in column B, but not A"
        End If
    Next j
End Sub