jeudi 2 août 2018

vba large if statement based on alot of comparables, any smarter way to do it?

i have a rather large code, which started small but as the variables kept coming so did the code.

my first "problem" is this part:

If (Cells(k, 5) = buafd1 Or Cells(k, 5) = buafd2 Or Cells(k, 5) = buafd3 _
Or Cells(k, 5) = buafd4 Or Cells(k, 5) = buafd5 Or Cells(k, 5) = buafd6 Or Cells(k, 5) = buafd7 _
Or Cells(k, 5) = buafd8 Or Cells(k, 5) = buafd9 Or Cells(k, 5) = buafd10 Or Cells(k, 5) = buafd11 _
Or Cells(k, 5) = buafd12 Or Cells(k, 5) = buafd13) And Cells(k, 6) = LCSPnavn1 Then

                    Amount = Cells(k, 13)
                    LCSPsum1 = LCSPsum1 + Amount

as you can see the cell that i look at is the same, but i am checking it against a list of variables which is a criteria for the sum function to be activated

the next thing is that i have alot of "LCSPsums"

like this:

'LCSPsum 2

                ElseIf (Cells(k, 5) = buafd1 Or Cells(k, 5) = buafd2 Or Cells(k, 5) = buafd3 _
Or Cells(k, 5) = buafd4 Or Cells(k, 5) = buafd5 Or Cells(k, 5) = buafd6 Or Cells(k, 5) = buafd7 _
Or Cells(k, 5) = buafd8 Or Cells(k, 5) = buafd9 Or Cells(k, 5) = buafd10 Or Cells(k, 5) = buafd11 _
Or Cells(k, 5) = buafd12 Or Cells(k, 5) = buafd13) And Cells(k, 6) = LCSPnavn2 Then

                    Amount = Cells(k, 13)
                    LCSPsum2 = LCSPsum2 + Amount

'LCSPsum 3
                ElseIf (Cells(k, 5) = buafd1 Or Cells(k, 5) = buafd2 Or Cells(k, 5) = buafd3 _
Or Cells(k, 5) = buafd4 Or Cells(k, 5) = buafd5 Or Cells(k, 5) = buafd6 Or Cells(k, 5) = buafd7 _
Or Cells(k, 5) = buafd8 Or Cells(k, 5) = buafd9 Or Cells(k, 5) = buafd10 Or Cells(k, 5) = buafd11 _
Or Cells(k, 5) = buafd12 Or Cells(k, 5) = buafd13) And Cells(k, 6) = LCSPnavn3 Then

                    Amount = Cells(k, 13)
                    LCSPsum3 = LCSPsum3 + Amount

all the way to 28 xD

it is working but i am now trying to put more "buafd" on which is a pretty slow process since i have to add 7 times "cells(k,5) = buafd..." 28 times.

Does someone have a smart solution that might also make it work faster?

regards Niklas

Aucun commentaire:

Enregistrer un commentaire