change contents of column c if column d matches any of about 20 phrases

[SableFlat]

I have csv files with up to 100,000 lines. I want to change the value of column C from Visual to Pass (or leave it alone if it already says Pass) based on whether column D on each line contains one of about 20 different phrases (most of these are long phrases). The list of phrases which should invoke a change from Visual to Pass is in a separate worksheet -- or I can add it to another tab in the same worksheet (I have 20+ files to check on a monthly basis). Columns A and B should not be touched.

Currently I am manually checking and changing Visual to Pass, but there must be an easier/faster way. I've searched for similar problems, but can't find any. I'm just a beginner with macros and VBA, so would appreciate any suggestions.

Thanks!

 

[bob33]

This should work; others may have a better way:

Sub DataMatcher()Dim j, n As Integer
Dim LRow As Long
Dim phrasestotest, stockphrases As Worksheet
Dim data1, data2 As String

Application.ScreenUpdating = False
Sheets("phrasestotest").Activate

LRow = Cells(Rows.Count, "d").End(xlUp).Row   'gets total number of rows that will be searched
   
 For n = 2 To LRow  'assumes row 1 has column headings
      data1 = Sheets("phrasestotest").Cells(n, 4).Value
      
        For j = 2 To 4            'to loop through the range for a match
            data2 = Sheets("stockphrases").Cells(j, 1).Value
                        
             If data1 = data2 Then
                Sheets("phrasestotest").Cells(j, 3) = "Pass"
                
              End If
        Next j
  Next n
  
Beep
MsgBox ("Done")
Application.ScreenUpdating = True

End Sub

 

[SableFlat]

Thank you so much!! I will test this asap.

 

[bob33]

Thank you so much!! I will test this asap.

J will likely need to be cycled through more than 2 to 4 rows; those were just test values. Adjust as needed or write back if you need advice.

 

[SableFlat]

Hi bob33,

This worked for the first 28 lines (with one miss) of the spreadsheet. I have 25 "stockphrases" I am checking against and 678 lines in the phrasestotest worksheet. I can't determine whether this code is comparing all 25 stockiphrases against each row on the phrasestotest worksheet -- just don't know enough about how this code works.

But, I was thrilled to see even the 28 lines with column 3 changed from Visual to Pass!!


Sub DataMatcher()
Dim j, n As Integer
Dim LRow As Long
Dim phrasestotest, stockphrases As Worksheet
Dim data1, data2 As String


Application.ScreenUpdating = False
Sheets("phrasestotest").Activate


LRow = Cells(Rows.Count, "d").End(xlUp).Row 'gets total number of rows that will be searched

For n = 2 To LRow 'assumes row 1 has column headings
data1 = Sheets("phrasestotest").Cells(n, 4).Value

For j = 2 To 26 'to loop through the range for a match
data2 = Sheets("stockphrases").Cells(j, 1).Value

If data1 = data2 Then
Sheets("phrasestotest").Cells(j, 3) = "Pass"

End If
Next j
Next n

Beep
MsgBox ("Done")
Application.ScreenUpdating = True


End Sub





I could provide you with an actual spreadsheet to test if that would help!

Again, thank you so much for your assistance!!!

 

[bob33]

Sure, send the file.

 

[SableFlat]

The code bob33 provided works partially...anyone else have any ideas??

Thanks!!!!!

 

[SableFlat]

With a couple of minor changes, this code now works!! Thanks again, bob33.

Main change:

If data1 = data2 Then
Sheets("phrasestotest").Cells(n, 3) = "Pass" 'changed j to n