If ElseIf And Or functions VBA

Question

I have a really long IF AND OR formula that I'm trying to convert to VBA so it's quicker.

=IF(OR(H10<>"GL402",H10<>"GL412",H10<>"GL422",H10<>"GL432",H10<>"GL442",H10<>"GL452",H10<>"GL492",
H10<>"GL480",H10<>"GL370",H10<>"GL380",H10<>"GL710")*AND(OR(D10<>3,D10<>9,D10<>10),E10<>"ASX",
F10<>"AUD"),"F126",(IF(OR(H2="GL402",H2="GL412",H2="GL422",H2="GL432",H2="GL442",H2="GL452",H2="GL492")*
AND(OR(D2<>"3",D2<>"9",D2<>"10"),E2="ASX",F2="AUD"),"D111",.......))

I thought this should look like:

IF range("H10").Value <>""GL402"" or ""GL412"" or ""GL422"" or ""GL432"" or ""GL442"" _
or ""GL452"" or ""GL492"" or ""GL480"" or ""GL370"" or ""GL380"" or ""GL710"" AND _
range("D10").Value <>3 or 9 or 10 and range("E10").Value <>""ASX"" and _
range("F10").Value <>""AUD""

then
    range("I10").Value = ""F126""

elseif
Range("H2").Value = ""GL402"" Or ""GL412"" Or ""GL422"" Or ""GL432"" Or ""GL442"" Or ""GL452"" Or ""GL492"" _
And Range("D2").Value <> 3 Or 9 Or 10 And Range("E2").Value = ""ASX"" And Range("F2").Value = ""AUD""

then
    Range("I2").Value = ""D111""
elseif
another lengthy conditions with ANDs and ORs

plus I was hoping to loop this so it applies this whole IF formula until the value of cell A (whichever row) is blank.

I sort of know the loop should be

Do .........
next (with something like A1 + 1)
until A1 + 1 = ""
loop

any help appreciated!

Your OR condition doesn't make sense - unless all these values (or cell addresses) are indentical, it will always be FALSE. — brettdj
– brettdj, Commented Nov 20, 2013 at 6:38

Floris · Accepted Answer · 2013-11-20 06:36:00Z

The first rule of good code is that it should be clear - easy to read and debug. Only afterwards do you try to make it "fast". Converting your current expression to VBA may give a speed advantage but you still don't meet the first test...

You can make things cleaner with an expression like this (you can put this right in your spreadsheet):

=ISERROR(MATCH(H10,{"GL402","GL412","GL422","GL432","GL442","GL452","GL492","GL480","GL370","GL380","GL710"},0))

This will evaluate to "true" if the the value in H10 does not match any of the values in the array. When you have a lot of or conditions in parallel, you can basically stop when the first condition is true.

An expression like that can be written in VBA as follows:

Sub test()

    Dim matchStrings
    Dim match1, match2

    matchStrings = Array("GL402", "GL412", "GL422", "GL432", "GL442", "GL452", "GL492", "GL480", "GL370", "GL380", "GL710")

    firstPart = Application.Match(Range("H10"), matchStrings, 0)
    If IsError(firstPart) Then
      MsgBox "no match found"
    Else
      match1 = true
      MsgBox "match found at index " & firstPart
    End If

End Sub

You can repeat similar code with other expressions, building match2, match3, etc - then combining with all the And and Or that you would like - for example

If match1 And (match2 Or match3) Then
  ... do something
Else
  ... do something else
End If

Jean-François Corbett · Accepted Answer · 2013-11-20 07:53:22Z

0

This won't work as expected:

If x = 1 Or 2 Or 3 Then
    MsgBox "x is either 1, 2, or 3"
End If

because 2 and 3 aren't boolean (true/false) conditions (at least not the way you expect them to be).

The proper syntax is:

If x = 1 Or x = 2 Or x = 3 Then
    MsgBox "x is either 1, 2, or 3"
End If

This is only a partial answer that nevertheless does address one of the many issues in your code.

answered Nov 20, 2013 at 7:53

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

Collectives™ on Stack Overflow

If ElseIf And Or functions VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related