Comparing two columns against two columns (There has to be a better way of doing this!)

HTMLGhozt

New Member
Joined
Jun 4, 2015
Messages
38
This module compares columns "S"&"Q" and "Z"&"I" against each other. I am not sure why it works like this; however, this code produces the enrll_Mismatch_Msg only when they match at the moment. I managed to write a short snippet which fixes this; however, it seems to be unreliable. I'm looking for code that can compare two columns against another two columns and produce a message in a fifth column.


Code:
Sub Cost_Match(ws2) 'refers to a worksheet
Dim My_Rows As Integer, My_New_Rows As Integer
Dim My_Text As String, my_text_2 As String
Dim My_Found_B As Integer
Dim Not_Found
Dim enrll_Mismatch_Msg As String


enrll_Mismatch_Msg = "."


    For My_Rows = 4 To Range("S" & Rows.Count).End(xlUp).Row
        My_Text = Range("S" & My_Rows).Value & Range("Q" & My_Rows).Value
        For My_New_Rows = 4 To Range("Z" & Rows.Count).End(xlUp).Row
            my_text_2 = Range("Z" & My_New_Rows).Value & Range("I" & My_New_Rows).Value
            If My_Text = my_text_2 Then
                My_Found_B = My_Found_B + 1
                If My_Found_B = 1 Then
                Range("W" & My_Rows) = enrll_Mismatch_Msg
                Else
                Range("W" & My_Rows) = enrll_Mismatch_Msg
                End If


                End If
        Next My_New_Rows
    Next My_Rows


    Call Cost_Matching_Fix(ws2)


End Sub
Sub Cost_Matching_Fix(ws2)
Dim i As Integer


With ws2
For i = 4 To Range("S" & Rows.Count).End(xlUp).Row
    If Range("W" & i).Value = "." Then
    Range("X" & i).ClearContents
    Else
    If Not IsEmpty(Range("T" & i)) Then
    Range("X" & i).ClearContents
    Else
    Range("X" & i) = "Cost Mismatch"
    End If
    End If
    Next i
End With
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top