Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel: If Data then Yes otherwise No, Macro?

Posted on 2012-03-14
14
Medium Priority
?
231 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
Independent Software Vendors: 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!

 
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 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

618 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