build If statement condition dynamically

**newbie_vba** · 10-13-2008, 11:48 AM

Hello,
This is my first post and hope this is not a silly question.

Is it possible to create a criteria for an if or case statement dynamically?

For example, if the string variable 'criteria' is literally "Sheet1.Cells.Range(A1).value = "A" AND Sheet1.Cells.Range(B1) <> "B", I would like to put:

Option #1:

Please Login or Register  to view this content.

actual result: Type mismatch error
desired result: delete row based on criteria passed

Option #2:
use a case statement?

Please Login or Register  to view this content.

actual result: deletes row regardless of criteria being passed
desired result: delete row based on criteria passed

Thanks in advance.

**Simon Lloyd** · 10-13-2008, 12:26 PM

Do you mean like this?:

Please Login or Register  to view this content.

**newbie_vba** · 10-13-2008, 01:20 PM

Hi Simon,
Correct, that is what I want to do. However, I do not want to re-set the actual criteria within the variable "Criteria" as it is already passed to the subroutine.

This way, I would like the subroutine to be more flexible so that if the criteria changes, you don't have to recode.

**Simon Lloyd** · 10-13-2008, 01:22 PM

All hard coded ranges need to be recoded, what is it you are trying to achieve? can you explain a little more?

**newbie_vba** · 10-13-2008, 01:36 PM

Hi Simon,
I would like to go through all rows in a file, applying conditions to be evaluated. If those conditions are true, delete that row.

How can I give the user the ability to specify the condition (via input box, or form) so that it is dynamic?

For example, they may want to specify that if row A = "x" delete the row in one instance. In another instance, they may select row A = "y" and row B = "z"

Sorry for the confusion.

**Simon Lloyd** · 10-13-2008, 02:05 PM

f course that kind of manipulation has to be done as a batch procees i.e do A=x for all of A first then A=y B=z next for all of A & B then...etci suggest a userform with a few pairs of textboxes for the criteria and perhaps checkboxes to signify which pairs will be processed, the user would enter all their pairings or criteria they want processed then check the checkboxes....etc