Solved

Excel: If Data then Yes otherwise No, Macro?

Posted on 2012-03-14
14
201 Views
Last Modified: 2012-03-14
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!
0
Comment
Question by:Pancake_Effect
14 Comments
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 
LVL 4

Author Comment

by:Pancake_Effect
Comment Utility
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
 
LVL 9

Expert Comment

by:experts1
Comment Utility
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
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
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
 
LVL 4

Author Comment

by:Pancake_Effect
Comment Utility
It's working wonderfully! Badotz, out of curiosity what does the select do?
0
 
LVL 4

Author Comment

by:Pancake_Effect
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 29

Expert Comment

by:Badotz
Comment Utility
>>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
 
LVL 29

Accepted Solution

by:
Badotz earned 500 total points
Comment Utility
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
 
LVL 4

Author Comment

by:Pancake_Effect
Comment Utility
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
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
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
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
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
 
LVL 4

Author Closing Comment

by:Pancake_Effect
Comment Utility
Thanks for the help!
0
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
No worries - glad to help.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now