?
Solved

Excel: If Data then Yes otherwise No, Macro?

Posted on 2012-03-14
14
Medium Priority
?
238 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
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 34

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
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…

862 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