Solved

Excel: If Data then Yes otherwise No, Macro?

Posted on 2012-03-14
14
214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 29

Expert Comment

by:Badotz
ID: 37720480
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 33

Expert Comment

by:Rob Henson
ID: 37720516
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
ID: 37720539
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 9

Expert Comment

by:experts1
ID: 37720669
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
ID: 37720695
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
ID: 37720712
It's working wonderfully! Badotz, out of curiosity what does the select do?
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37720813
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
 
LVL 29

Expert Comment

by:Badotz
ID: 37720842
>>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
ID: 37720854
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
ID: 37720926
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
ID: 37721028
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
ID: 37721029
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
ID: 37721256
Thanks for the help!
0
 
LVL 29

Expert Comment

by:Badotz
ID: 37721269
No worries - glad to help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

717 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