Assigning Not Null to Variable

psiemens12 · September 3rd, 2010, 02:43 PM

My function has got 4 variables(L1, L2, L3 & L4), each with a value pertaing to 4 columns(A, B, C & D). Based on the values sent to the function as parameters to L1, L2, L3 & L4, the respective rows have to be selected.

When I assign "All" to L2, the function should select the rows only based on the values in L1, L3 & L4. column B should not be considered for the row selection. For example,

Sub Test(L1, L2, L3, L4)
' if L2="All" the following if condition shd not check column B.

for i=1 to 10
if .cells(i,1)=L1 and .cells(i,2)=L2 and .cells(i,3)=L3 and .cells(i,4)=L4
Some Action.....
Endif
next i
End Sub

Old Pedant · September 3rd, 2010, 03:12 PM

Not completely sure I understand you, but how about this:

Code:

Sub Test(L1, L2, L3, L4)

    FOR i=1 To 10
        IF     ( L1="All" OR L1= .cells(i,1) ) _
           AND ( L2="All" OR L2= .cells(i,2) ) _
           AND ( L3="All" OR L3= .cells(i,3) ) _
           AND ( L4="All" OR L4= .cells(i,4) ) _
        THEN
            Some Action.....
        End If
    Next i
End Sub

psiemens12 · September 3rd, 2010, 03:16 PM

Hey, thanks for your prompt reply.

when L2="All",
it should select rows based on other values from L1, L3 & L4, mapping to columns A, C & D respectively.

the condition
if .cells(i,1)=L1 and .cells(i,2)=L2 and .cells(i,3)=L3 and .cells(i,4)=L4
endif
should get satisfied with any value in column B

Old Pedant · September 3rd, 2010, 03:59 PM

Yes??? I admit I assumed you would want to do the same with the other L values. That is, if L4 is "All" then it, too, is essentially ignored in qualifying a cell. Same for L1 and L3.

Did you try my answer? Did it work?

If you really *ONLY* wanted to check for L2, not for the other L values, then:

Code:

Sub Test(L1, L2, L3, L4)

    FOR i=1 To 10
        IF     ( L1= .cells(i,1) ) _
           AND ( L2="All" OR L2= .cells(i,2) ) _
           AND ( L3= .cells(i,3) ) _
           AND ( L4= .cells(i,4) ) _
        THEN
            Some Action.....
        End If
    Next i
End Sub

psiemens12 · September 4th, 2010, 12:23 AM

Hey, I got that, its working. I wonder how i didn't get this before. Thanks a lot, you made my work easier. Once again thanks a lot.

Premkumar.