Excel: If Data then Yes otherwise No, Macro?

This is a snip of my macro I'm using for another part of the document at work, it works great there but I need it to do more, maybe someone can revise it? Or it might require a new one:

Next
Application.ScreenUpdating = False
Set rg = Intersect(Range("C:C"), ActiveSheet.UsedRange)
For Each c In rg.Cells          'Only search column C
    Select Case c.Value
        Case "CT"              'Old value
            c.Value = "Y"         'New value
            
    End Select
Next

Open in new window


Basically if anything says CT  it needs to be REPLACED to "Y" (for yes)

The problem with this script is I need it to do one more thing.

If the data in that cell does not say "CT" and says "MRI" for example I need "MRI" replaced to "N" (for No)

Sorry I'm a noob at this!
LVL 4
Pancake_EffectAsked:
Who is Participating?
 
BadotzConnect With a Mentor Commented:
This should stop when the cell value in row 'n' is blank ("")
Next
Application.ScreenUpdating = False
Set rg = Intersect(Range("C:C"), ActiveSheet.UsedRange)
For Each c In rg.Cells          'Only search column C
    If Cells(c.Row(), 1) = "" then Exit For '' Are we finished?

    If c.Value = "CT" Then
        c.Value = "Y"
    Else
        c.Value = "N"
    End If
Next

Open in new window

0
 
BadotzCommented:
Try this
Next
Application.ScreenUpdating = False
Set rg = Intersect(Range("C:C"), ActiveSheet.UsedRange)
For Each c In rg.Cells          'Only search column C
    Select Case c.Value
        Case "CT"              'Old value
            c.Value = "Y"      'New value
        Case "MRI"             'Old value
            c.Value = "N"      'New value
    End Select
Next

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Try this instead:

For Each c In rg.Cells          'Only search column C
    Select Case c.Value = "CT"
            c.Value = "Y"         'New value

    Select Case c.Value = "MRI"
            c.Value = "N"
           
    End Select
Next

Rob H
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Pancake_EffectAuthor Commented:
I was using MRI as an example of one of many choices, there are A LOT of other medical codes, is there a way I can just make it say, only CT is yes, otherwise all other is no?
0
 
experts1Commented:
Case "Else" will catch all other values as below:

Next
Application.ScreenUpdating = False
Set rg = Intersect(Range("C:C"), ActiveSheet.UsedRange)
For Each c In rg.Cells          'Only search column C
    Select Case c.Value
        Case "CT"              'Old value
            c.Value = "Y"         'New value
        Case "MRI"              'Old value
            c.Value = "N"         'New value
        Case Else              'Any other value
            c.Value = "N"         'New value
    End Select
Next

Open in new window

0
 
BadotzCommented:
No need for SELECT...CASE
Next
Application.ScreenUpdating = False
Set rg = Intersect(Range("C:C"), ActiveSheet.UsedRange)
For Each c In rg.Cells          'Only search column C
    If c.Value = "CT" Then
        c.Value = "Y"
    Else
        c.Value = "N"
    End If
Next

Open in new window

0
 
Pancake_EffectAuthor Commented:
It's working wonderfully! Badotz, out of curiosity what does the select do?
0
 
Pancake_EffectAuthor Commented:
Edit:

There is a small problem that can be fixed manually, but if a simple code can fix it, that would make everything A LOT smoother.

Can I have this code also check to see if Column A's data, and only have Y or N check go as far down as A's data goes?

Because otherwise it runs "N" all the way down forever.
0
 
BadotzCommented:
>>what does the select do?

Select...End Select allows you to test a single variable for many different values. You can accomplish the same thing with If...ElseIf...End If statements, but Select requires less coding and is (arguably) easier on the eyes.
0
 
Pancake_EffectAuthor Commented:
Thanks Badotz you did it again, works like a charm.

Again just out of curiosity (I'm trying to start learn coding myself)

I see *** If Cells(c.Row(), 1) = "" then Exit For '' *** is the line where it checks and cuts it off.  

From my understanding it checks to see if the entire row is empty, if so make it blank? Is that theory correct?

Thanks!
0
 
BadotzCommented:
That's a bit too broad a view.

The format is Cells(Row, Column), so Cells(c.Row(), 1) refers to the cell at Range c.Row() and Column 1, or "An" where n is the Row.
0
 
BadotzCommented:
That's a bit too broad a view.

The format is Cells(Row, Column), so Cells(c.Row(), 1) refers to the cell at Range c.Row() and Column 1, or "An" where n is the Row.
0
 
Pancake_EffectAuthor Commented:
Thanks for the help!
0
 
BadotzCommented:
No worries - glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.