Solved

# Excel Macro for If condition

Posted on 2014-12-31
98 Views
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
Question by:mldaigle1
• 4
• 4
• 3
• +2

LVL 25

Expert Comment

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")
``````
0

LVL 48

Expert Comment

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
``````
Regards
0

LVL 37

Expert Comment

No macro, just a simple formula

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

LVL 25

Expert Comment

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")
``````
0

LVL 37

Expert Comment

Oops, capital letter!!

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

LVL 33

Expert Comment

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

Author Comment

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 33

Expert Comment

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

The question seems to be changing...
0

Author Comment

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
sample.txt
0

LVL 33

Expert Comment

Can you upload a workbook rather than a text file?:)
0

Author Comment

of course

:)
sample.xlsx
0

LVL 33

Accepted Solution

Norie earned 500 total points
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
``````

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

Author Closing Comment

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

Enums (shorthand for â€˜enumerationsâ€™) are not often used by programmers but they can be quite valuable when they are. Â What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that containsâ€¦
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.