Out of control IF Statement

[Pablo_TX]

I am trying to concatenate a number of cells in order to create a product title, Basic Data Text. These titles can contain up to eight cells based on a few conditions. There are two worksheets involved, Basic_Data_Text and Request. My IF statement is out of control and I need some help.

I am an VBA newbie and often use the recorder to build things. This thing seems to have gotten out of hand so any help is greatly appreciated. As you can see my IF Statement is whacked out.

Attached is my worksheets and a Word doc with some thoughts but things I am stumped on.

Thank you
Pablo

[dflak]

Two things:

1. The code should be in its own module and not in the ThisWorkbook module. Go to the editor, select Insert -> Module then cut and paste the code into the new module.

2. Provide a verbal specification of what you are trying to accomplish with the if statement. This will save us more than half the trouble. Otherwise the first step is to reverse engineer your intent. It would be a lot easier if you just told us .


It looks like you are trying to get the VB code to enter a formula into a cell and that the reason for this is that part of the formula is to compare if the contents of a cell matches a value on a variable sized list. There are better ways of doing this and they do not involve VBA.

If you want to test if a value is on a list of values then =ISNUMBER(MATCH(Value,List-of-Values,0)) will yield true if the value is on the list or false if it is not. Furthermore List-of-Values can be dynamic: make use of Excel Tables.

If you took the information on the Request Sheet and made it into a Table then you could do several things. Tables know how big they are. You can eliminate rows 17 to 298 and don't worry about the data validation and formulas because tables also remember and copy down all this stuff automatically when you add a new row directly below the table.

Then you can use formulas like: =ISNUMBER(MATCH(A21,Table1[Product Hierarchy],0)) as you add or delete data to the table, the formula will adjust to reference the exact number of rows it needs.

Referencing Table parts is also possible in VBA. It makes the code a lot easier to understand and debug.

Here is more information about tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

We'll await your specification as to what you are looking for in the IF statement.

[Bernie Deitrick]

You can convert your formula section by section to VBA code, like so. This part:

Please Login or Register  to view this content.

Becomes code like this:

Please Login or Register  to view this content.