• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

Excel Macro for If condition

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
mldaigle1
Asked:
mldaigle1
  • 4
  • 4
  • 3
  • +2
1 Solution
 
Shaun KlineLead Software EngineerCommented:
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
 
Rgonzo1971Commented:
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
 
Neil RussellTechnical Development LeadCommented:
No macro, just a simple formula

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

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Shaun KlineLead Software EngineerCommented:
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
 
Neil RussellTechnical Development LeadCommented:
Oops, capital letter!!

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

Open in new window

0
 
NorieVBA ExpertCommented:
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
 
mldaigle1Author Commented:
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
 
NorieVBA ExpertCommented:
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
 
Neil RussellTechnical Development LeadCommented:
The question seems to be changing...
0
 
mldaigle1Author Commented:
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
 
NorieVBA ExpertCommented:
Can you upload a workbook rather than a text file?:)
0
 
mldaigle1Author Commented:
of course

:)
sample.xlsx
0
 
NorieVBA ExpertCommented:
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
 
mldaigle1Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now