Solved

Excel Macro for If condition

Posted on 2014-12-31
14
109 Views
Last Modified: 2015-01-05
Hi,

Here my need:

for each row of the table, verify
              if  
                       (Cell Gxx (which is the manufacturer) the value is equal to "VMware inc" or "Microsoft Corporation"
                           or
                        Cell Uxx is equal to lowercase "trader")
                  then write value "Yes" in Cell XX (which is the cell of last column of the table)
                  else write value "No" in Cell XX (which is the cell of last column of the table)
             
Does it sound clear?
0
Comment
Question by:mldaigle1
[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
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40525448
Do you truly need a macro, or would a formula work?
=IF(OR(G2="VMware inc", G2="Microsoft Corporation", NOT(ISERR(FIND("trader", U2)))), "Yes", "No")

Open in new window

0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 40525449
HI,

pls try

Sub Macro()
LastCol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    If Range("G" & c.Row) = "VMware Inc" Or _
            Range("G" & c.Row) = "Microsoft Corporation" Or _
            LCase(Range("U" & c.Row)) = "trader" Then
        Cells(c.Row, LastCol) = "Yes"
    Else
        Cells(c.Row, LastCol) = "No"
    End If
Next
End Sub

Open in new window

Regards
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40525451
No macro, just a simple formula

=IF(OR(OR(G1="Microsoft",G1="Vmware"),U1="Trader"),"YES","No")

Open in new window

0
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!

 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40525453
Noticed I missed the lower case requirement in my formula:
=IF(OR(G2="VMware inc", G2="Microsoft Corporation", NOT(ISERR(FIND("trader", LOWER(U2))))), "Yes", "No")

Open in new window

0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40525455
Oops, capital letter!!

=IF(OR(OR(G1="Microsoft Corporation",G1="VMware inc"),U1="trader"),"YES","No")

Open in new window

0
 
LVL 34

Expert Comment

by:Norie
ID: 40525461
Perhaps this formula in the next empt column copied down.

=IF(OR(G2="VMware inc", G2="Microsoft Corporation", U2="trader"), "Yes", "No")

Which in code would be this.
Dim rng As Range

    With Sheets("Sheet1")    ' change Sheet1 to relevant sheet
        ' find next empty column  in first row
        Set rng = .Cells(1, .Columns.Count).End(xlToLeft).Offset(, 1)    
        Set rng = rng.Resize(.Range("G" & .Rows.Count).End(xlUp).Row - 1)
        With rng
            .Formula = "=IF(OR(G2=""VMware inc"", G2=""Microsoft Corporation"", U2=""trader""), ""Yes"", ""No"")"
            .Value = .Value
        End With
    End With

Open in new window

0
 

Author Comment

by:mldaigle1
ID: 40525588
Thanks guys for your help but i really needed a macro since i will chain it with other macros.

imnorie,
your macro provide me a result, but not what i expected.


I optimize the macro as follow:

Dim rng As Range
Sub Exempt()
    With Sheets("Table")
        ' find next empty column  in second row
        Set rng = .Cells(2, .Columns.Count).End(xlToLeft).Offset(, 1)
        Set rng = rng.Resize(.Range("G" & .Rows.Count).End(xlUp).Row - 1)
        With rng
            .Formula = "=IF(OR(G2=""VMware, Inc."", G2=""Microsoft Corporation"", U2=""Yes""), ""Yes"", ""No"")"
            .Value = .Value
        End With
    End With
End Sub


but it only worked for U2=Yes, and totally ignore the manufacturer and wrote as result "#N/A" and not "No".....
0
 
LVL 34

Expert Comment

by:Norie
ID: 40525622
I can't see how the code I posted could produce results like that.

Do you have any error values in column G or column U?

Could you attach a small sample workbook?

By the way, why did you use .Cells(2, Columns.Count) instead of Cells(1, Columns.Count)?

Also why did you change U2="trader" to U2="Yes"?
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40525635
The question seems to be changing...
0
 

Author Comment

by:mldaigle1
ID: 40525736
cell1 is my header
i modify the value from trader to yes in the original report, so i modify it in the formula as well
sample attach as requested
thanks for your help
sample.txt
0
 
LVL 34

Expert Comment

by:Norie
ID: 40525748
Can you upload a workbook rather than a text file?:)
0
 

Author Comment

by:mldaigle1
ID: 40525771
of course

:)
sample.xlsx
0
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 40525819
The problem is the #N/A values in columns G and U, if there is an #N/A in either columns the formula will return #N/A too.

Not sure how you want to handle that but if you changed the formula to this.

=IFERROR(IF(OR(G2="VMware, Inc.", G2="Microsoft Corporation",IFERROR(U2="Yes",FALSE)), "Yes", "No"), "No")

then the #N/A's would become 'No's.

Here's the code for that change.
Dim rng As Range
Sub Exempt()
    With Sheets(1)
        ' find next empty column  in second row
        Set rng = .Cells(2, .Columns.Count).End(xlToLeft).Offset(, 1)
        Set rng = rng.Resize(.Range("G" & .Rows.Count).End(xlUp).Row - 1)
        With rng
            .Formula = "=IFERROR(IF(OR(G2=""VMware, Inc."", G2=""Microsoft Corporation"",IFERROR(U2=""Yes"",FALSE)), ""Yes"", ""No""), ""No"")"
            .Value = .Value
        End With
    End With
End Sub

Open in new window


If that's now what you want I'm sure the formula can be changed.
0
 

Author Closing Comment

by:mldaigle1
ID: 40531275
Thanks, works like a charm

Happy New Year!

:)

I now have a complete table to play with in order to provide stats to my boss.  
Thanks again everybody!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 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