Hello again!
This community has been so helpful with my previous problems I've returned with another. This could be something simple; however, I can't seem to figure it out.
I stole this code (off this site?) and I'm trying to re-work it to compare two columns based off of the position of names in other columns. [TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Names1[/TD]
[TD]Other stuff[/TD]
[TD]Other stuff[/TD]
[TD]Date1[/TD]
[TD]other stuff[/TD]
[TD]Date 2[/TD]
[TD]other stuff[/TD]
[TD]Names2[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/1/1991[/TD]
[TD][/TD]
[TD]1/2/1991[/TD]
[TD][/TD]
[TD]James Smith[/TD]
[TD]Date Mismatch[/TD]
[/TR]
[TR]
[TD]Jr. Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/13/2001[/TD]
[TD][/TD]
[TD]1/13/2001[/TD]
[TD][/TD]
[TD]Jr. Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/2/1991[/TD]
[TD][/TD]
[TD]4/1/1991[/TD]
[TD][/TD]
[TD]John Smith[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The output needs to be based off the names in "Names1". I was trying to base the rows off of string members... but in the end (after troubleshooting)... Sometimes it's just better to do it simply.
The code as it is simply spits out Date mismatch on each row.
Also, if at all possible the code needs to be somewhat ambiguous there'll be a few processes that I'll be adjusting it too.
This community has been so helpful with my previous problems I've returned with another. This could be something simple; however, I can't seem to figure it out.
I stole this code (off this site?) and I'm trying to re-work it to compare two columns based off of the position of names in other columns. [TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Names1[/TD]
[TD]Other stuff[/TD]
[TD]Other stuff[/TD]
[TD]Date1[/TD]
[TD]other stuff[/TD]
[TD]Date 2[/TD]
[TD]other stuff[/TD]
[TD]Names2[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/1/1991[/TD]
[TD][/TD]
[TD]1/2/1991[/TD]
[TD][/TD]
[TD]James Smith[/TD]
[TD]Date Mismatch[/TD]
[/TR]
[TR]
[TD]Jr. Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/13/2001[/TD]
[TD][/TD]
[TD]1/13/2001[/TD]
[TD][/TD]
[TD]Jr. Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/2/1991[/TD]
[TD][/TD]
[TD]4/1/1991[/TD]
[TD][/TD]
[TD]John Smith[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The output needs to be based off the names in "Names1". I was trying to base the rows off of string members... but in the end (after troubleshooting)... Sometimes it's just better to do it simply.
The code as it is simply spits out Date mismatch on each row.
Also, if at all possible the code needs to be somewhat ambiguous there'll be a few processes that I'll be adjusting it too.
Code:
Sub Compare_Eff_Date()
Dim ws2 As Worksheet
Dim MbrName_Console As String 'Member Names (Console)
Dim MbrName_Carrier As String 'Member Names (Carrier)
Dim Console_Date As String 'Console Effective Date
Dim Carrier_Date As String 'Carrier Effective Date
Dim Carrier_Tg As String
Dim Console_Tg As String
Dim Eff_Date_Mismatch As String 'Effective Date Mismatch
Dim iListStart As Integer 'Row where List Begins
Dim Eff_Date_Mismatch_Msg As String
Dim i As Integer, j As Integer
Dim iLastRow1 As Integer, iLastRow2 As Integer
Set ws2 = ThisWorkbook.Sheets("Master spreadsheet")
'---Edit these variables---'
MbrName_Console = "S" 'Member Names (Console)
MbrName_Carrier = "Y" 'Member Names (Carrier)
Console_Date = "P" 'Console Effective Date
Carrier_Date = "H" 'Effective Date
Carrier_Tg = (MbrName_Carrier & Carrier_Date & i)
Console_Tg = (MbrName_Console & Console_Date & j)
Eff_Date_Mismatch = "V" '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
Eff_Date_Mismatch_Msg = "Effective Date Mismatch"
'Identify in Console not Portal
For i = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2)
For j = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2)
If (Range("S" & j) & ("P" & j)) = (Range("Y" & i) & ("H" & i)) Then
Exit For
ElseIf j = WorksheetFunction.Min(iLastRow1, iLastRow2) Then
Range(Eff_Date_Mismatch & i) = Eff_Date_Mismatch_Msg
End If
Next j
Next i
End With
End Sub
Last edited: