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

SableFlat

New Member
Joined
Dec 17, 2014
Messages
5
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

bob33

Board Regular
Joined
Oct 28, 2011
Messages
55
This should work; others may have a better way:
Code:
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
 
Upvote 0

bob33

Board Regular
Joined
Oct 28, 2011
Messages
55
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.
 
Last edited:
Upvote 0

SableFlat

New Member
Joined
Dec 17, 2014
Messages
5
ADVERTISEMENT
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!!!
 
Upvote 0

SableFlat

New Member
Joined
Dec 17, 2014
Messages
5
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
 
Upvote 0

Forum statistics

Threads
1,195,960
Messages
6,012,566
Members
441,710
Latest member
needhelp_please

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
Top