Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 3rd, 2010, 02:43 PM
Registered User
 
Join Date: Sep 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Assigning Not Null to Variable

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 September 3rd, 2010, 03:12 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
 
Old September 3rd, 2010, 03:16 PM
Registered User
 
Join Date: Sep 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 September 3rd, 2010, 03:59 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
 
Old September 4th, 2010, 12:23 AM
Registered User
 
Join Date: Sep 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Assigning Not Null to Variable

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
ASSIGNING A JAVA SCRIPT VARIABLE TO A XSL VARIABLE SOMANATHAN10 XSLT 1 February 21st, 2007 04:26 AM
Assigning value from variable cyberddindia Classic ASP Basics 3 November 7th, 2006 11:57 AM
Assigning null values in typed datasets Utsav.Kedia C# 0 January 16th, 2006 10:03 PM
Assigning null values in typed data sets Utsav.Kedia General .NET 0 January 16th, 2006 03:02 PM
Help with assigning a variable from a binding dfranczak ADO.NET 0 July 17th, 2003 01:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.