• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Can the actual text of an IF statement be stored as a variable

D

dnessim

Member
Hi all,

I wrote a vba app that opens a master workbook, it does a complicated IF statement to filter out rows based on column criteria

It then saves the filtered rows to another workbook , closes out the master, closes and saves out the subset of data as "work list"

this all works great but now I need to have several other "work lists" created and each one has a different set of criteria.


So I updated my user form to have 3 checkboxes , the user can now select which "work list" they want or All 3.


I know i can just repeat my code 3 times but isn't there a smarter way to code this scenario


I want to be able to stick the IF statement in a variable just like we can do with SQL in ASP? this way all i need to do is swap out which

mylogicstring i need


Dim mylogicstring1 as string

dim mylogicstring2 as string


' pseudo code example

mylogicstring = "IF column G = "beer mugs" then IF column H = "" OR column P = "" OR Column S <> Column T end if"


do while column A <> ""

mylogicstring


loop


thanks !

Dave
 
Dave

I don't believe you can do what you want


Can you use a Function and pass it the different parameters as required?
 
Hi,

Well I thought about that too but thought I would have the same challenge

Can I create a function and have one of the inputs a text string that is my if statement ?


Thanks Dave
 
I don't believe you can pass the actual function to a Function as a text string

I will ask elsewhere later today
 
Thanks Hui,

Here is a snippet of code that will change depending on which worklist I want to create


'

'loop as long as we dont hit blank rows, might need a better way to iterate all rows. Not sure is the master list has a column that is never blank

Do While Cells(x, 8) <> ""


'logic test to build the worklist , this will change depending on criteria.


If Cells(x, 8) = "Closed - No RAC Denial" Then


If Cells(x, 11) = "" Or Cells(x, 12) <> Cells(x, 13) Or Cells(x, 14) = "" Or Cells(x, 16) = "" Or Cells(x, 17) = "" Or Cells(x, 24) = "" Then


'copy each row that meets the criteria set in the logic statement


I guess I will have to come up with another approach?

Thanks

Dave
 
Dave


If the If() statements are the same and just the Words "Closed - No RAC Denial" and cell references change, then this can be done in a Function()


If the actual structure of the If() statements changes in different scenarios better to just hard code them as required
 
Hi Hui,

Yes that is what I am thinking. The if statements are different on each scenario.

This is going to get complex. I will need to really keep this code organizied.......

Thanks for your help

Dave
 
You must log in or register to reply here.
Back
Top