CODEWITHSUNDEEP
excelvba
Priyanka Maduraiveeran
Priyanka Maduraiveeran

Reputation: 1

Multiple nested if(ISNUMBER(FIND... is required

Is it possible to do so?

Sub test()

r = ActiveSheet.Range("v" & Rows.Count).End(xlUp).Row
Range("$W2:$W" & r & " ").Formula=
  IF(ISNUMBER(FIND("7212",K2)),"laptop",
     IF(ISNUMBER(FIND("774",K2)),"laptop",
        IF(ISNUMBER(FIND("7745",K2)),"laptop",
           IF(ISNUMBER(FIND("234",K2)),"desktop","NO")))
End Sub

I have to apply conditions like this on if condition but I am unable to achieve this.

Kindly anyone help on this

Upvotes: 0

Views: 110

Answers (1)

Dominique
Dominique

Reputation: 17491

You need to enclose your formula in double quotes, but in order to do so, you need to double the double quotes within your formula, something like this:

Range("$W2:$W" & r & " ").Formula = "IF(ISNUMBER(FIND(""7212"",K2)),""laptop"", IF(ISNUMBER(FIND(""774"",K2)),""laptop"", IF(ISNUMBER(FIND(""7745"",K2)),""laptop"", IF(ISNUMBER(FIND(""234"",K2)),""desktop"",""NO"")))"

(Sorry for the bad formatting, but VBA does not allow multiline strings)

By the way, I've just edited your formula in order for it to work, but I don't understand your logic: your logic says:

If <condition1> 
then "laptop"
else if <condition2>
     then "laptop"
     else if <condition3>
          then "laptop"
          else "desktop"
          end if
     end if
end if

Generally, for such a situation, the following logic is used:

if <condition1> OR <condition2> OR <condition3>
then "laptop"
else "desktop"
end if

This reduces the number of if-loops and makes it more readable.

Upvotes: 2

Related Questions