Multiple IF criteria in VBA Excel

[alexgomez]

Hi,

I need your help related with an multiple criteria IF.

I'm trying to create an IF statement with multiple criteria, based on values from different columns.

Will try to be clear enough, as far as my english allows.

I need to have column (C) saying if it's "true" based on other columns criteria.

1 - Compare the column K with L. K has a number/text value. If exists in in both columns then "true" will be displayed in column C;
2 - Compare the column K with M. K has a number/text value. If exists in in both columns then "true" will be displayed in column C;
3 - Column B. Text only. If any part of the text has "col" or "PB" on any row of column B, then "true" will be displayed in column C;
4 - Column S. Has negative and positive numbers. If any value is >100 or < -100, then "true" will be displayed in column C;
5 - Column AG. Numbers only. If any value is >10 or < -10, then "true" will be displayed in column C;
6 - After this steps I need to sort the column C by "true";
7 - Color the "true" cells in yellow.

I believe this a simple IF statement, but I don't enough knowledge to write the code line.

Will this information helps?

I would be very appreciated if someone can help me on this.

Kindest regards,
Alex

 

[Hui]

Alex

Firstly, Welcome to the Chandoo.org Forums

it will be something like

If k=l then
c=True
elseif k=m then
c=True
elseif b="Col" or b="PB" then
c=True

.
.
.
else
c=something
end if

Then sort
Then Color
[code]

Attaching a file with data will allow a more targeted response

 

[Stevie]

Untested, but try this:

function func1(k as range, l as range, m as range, b as range, s as range, ag as range) as string
if k = l or k = m or b.text = "PB" or b.text = "col" or abs(s) > 100 or abs(ag) > 10 then
func1 = "true"
else:
func1 = "false"
end if
end function

Then in the worksheet C1 do:
=func1(K1,L1,M1,B1,AG1)

Copy the formula down the column as required, and use conditional formatting, and a filter to get the desired result.

 

[Marc L]

Hi !

A must see : Select Case statement …
​

 

[alexgomez]

Alex

Firstly, Welcome to the Chandoo.org Forums

it will be something like

If k=l then
c=True
elseif k=m then
c=True
elseif b="Col" or b="PB" then
c=True

.
.
.
else
c=something
end if

Then sort
Then Color
[code]

Attaching a file with data will allow a more targeted response

Hi Many thanks for your reply,

But it's not working...

I have written the code like this:

Range("C2:C" & lastRow).Select
ActiveCell.FormulaR1C1 = "=IF(RC[8]=RC[9],""True"")"
Range("C2").AutoFill Destination:=Range("C2:C" & lastRow)

But I'm not being able to add another criteria to the "ActiveCell.FormulaR1C1 = "=IF(RC[8]=RC[9],""True"")""

I tried with your suggestion, but nothing happens.

Also tried with Stevie suggestion, but it is not working also.

It would be easier to upload my worksheet, but the information is confidential, and I'm sure the company wouldn't allow me to do it. Could get fired if they found out.

Any suggestion to add the elseif to the IF condition I posted above?

Once again, many thanks for your help.

Best regards,

Alex

 

[alexgomez]

Alex

Firstly, Welcome to the Chandoo.org Forums

it will be something like

If k=l then
c=True
elseif k=m then
c=True
elseif b="Col" or b="PB" then
c=True

.
.
.
else
c=something
end if

Then sort
Then Color
[code]

Attaching a file with data will allow a more targeted response

Hi Hui,

Meanwhile I tried the following:
/Code
If (Range("K2").Value = Range("L2").Value) Then
Range("C2").Value = "True"
ElseIf (Range("K97").Value = Range("M97").Value) Then
Range("C97").Value = "True"

End If

It worked well for the row #2, but nothing happens on row #97 (and should give me "true".

I'm uploading a file without part of the confidential information.

I would much appreciated if someone can help me on this.

Best regards,
Alex

 

[YasserKhalil]

Hello Alex
Try this code

Sub Test()
    Dim I As Long
   
    Application.ScreenUpdating = False
        With Folha1
            .Range("C2:C1000").ClearContents
           
            For I = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
                If .Cells(I, "K").Text = .Cells(I, "L").Text Or _
                .Cells(I, "K").Text = .Cells(I, "M").Text Or _
                InStr(.Cells(I, "B").Text, "PB") Or InStr(.Cells(I, "B").Text, "col") Or _
                .Cells(I, "S") > 100 Or .Cells(I, "S") < -10 Or _
                .Cells(I, "AG") > 10 Or .Cells(I, "AG") < -10 Then
                    .Cells(I, "C").Value = "True"
                    .Cells(I, "C").Interior.ColorIndex = 6
                End If
            Next I
           
            With .Range("A1").CurrentRegion
                .Sort .Range("C2"), 1
            End With
        End With
    Application.ScreenUpdating = True
End Sub

 

[jindon]

See if this is how you wanted.

Sub test()
    With Sheets("Suspens Titres").Cells(1).CurrentRegion
        .Columns("c").Offset(1).Resize(.Rows.Count - 1).Formula = _
        "=rept(""True"",countif(l2:m2,k2)+sum(countif(b2,{""*col*"",""*BP*""}))" & _
                    "+or(s2<-100,s2>100)+or(ag2+0<-10,ag2+0>10)>0)"
        With .Columns("c").FormatConditions
            .Delete
            With .Add(Type:=xlExpression, Formula1:="=c1=""True""")
                .Interior.Color = vbYellow
            End With
        End With
    End With
End Sub

 

[achu]

Hello Alex
Try this code

Sub Test()
    Dim I As Long
  
    Application.ScreenUpdating = False
        With Folha1
            .Range("C2:C1000").ClearContents
          
            For I = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
                If .Cells(I, "K").Text = .Cells(I, "L").Text Or _
                .Cells(I, "K").Text = .Cells(I, "M").Text Or _
                InStr(.Cells(I, "B").Text, "PB") Or InStr(.Cells(I, "B").Text, "col") Or _
                .Cells(I, "S") > 100 Or .Cells(I, "S") < -10 Or _
                .Cells(I, "AG") > 10 Or .Cells(I, "AG") < -10 Then
                    .Cells(I, "C").Value = "True"
                    .Cells(I, "C").Interior.ColorIndex = 6
                End If
            Next I
          
            With .Range("A1").CurrentRegion
                .Sort .Range("C2"), 1
            End With
        End With
    Application.ScreenUpdating = True
End Sub

what is folha1 i dont undestand

 

[YasserKhalil]

It is like Sheet1 but Folha1 in Portuguese ..

 

[achu]

thanks,

 

[alexgomez]

Hi all,

Many thanks for your help. Both the codes presented by Yasser and Jindon, worked properly.

Apologies for this terrible late reply.

Kindest regards,

Alex