Can someone help me apply this if statement to a range of cells

**mk3ll00** · 05-31-2013, 07:52 AM

Here is the current if statement

Please Login or Register  to view this content.

I would like to apply it to A1:A2000. I'm fairly new and haven't been able to figure it out. Thank you in advance.

**TMS** · 05-31-2013, 08:07 AM

One way, maybe:

Please Login or Register  to view this content.

Regards, TMS

**mk3ll00** · 05-31-2013, 08:21 AM

Thank you for the quick response. The cells in column A have formulas in them they will return "", 1, or 3 depending on another range of cells.
I tried your suggestion but I'm getting an error message popup even when the cell in column A is blank?

**TMS** · 05-31-2013, 08:33 AM

The code as provided will display a MsgBox if ANY cell in the range A1:A2000 has, or returns, a value of 1 or 3. This check will happen when you change ANY cell on the worksheet. So, if you have a formula in the range that returns a 1, say, you'll get a pop-up message. If you click OK, the MsgBox will go but, as soon as you change any other cell, it carries out the check again and displays the Box. To not get the MsgBox displayed, you would have to clear the value either by deleting it or bu changing the input to the formulae taht generate the values.

Maybe I have mis-interpreted your requirement. What, exactly, do you want to happen when you have values of 1 and/or 3 in column A?

Regards, TMS

**mk3ll00** · 05-31-2013, 09:45 AM

I would like to create a couple different error messages that can pop up depending on the value that shows up. Even if every cell in column AA is blank which it currently is I'm still getting the error message when ever I alter the range of cells that are linked to column AA. In case I'm not explaining that well let's say column B is the column linked to AA. I type in the word stop into B1 which would promt cell AA1 to have a value of "". In this case I don't want an error message to pop up but it seems to be even if there isn't a value of 1 or 3 in the entire AA range

**TMS** · 05-31-2013, 10:05 AM

Please post a sample workbook.

Regards, TMS

**mk3ll00** · 05-31-2013, 10:12 AM

Polycast.xlsm

Column H is the column they will enter the function into. Column AA feeds from that.

**TMS** · 05-31-2013, 12:10 PM

OP: paragraph after the code excerpt:

I would like to apply it to A1:A2000.

That's the range that the code checks ... A1:A2000.

Looking at your sample, every row in column A has a 1 in it, hence you will get pop-ups.

Change the rCheck range from A1:A2000 to AA1:AA2000 and it should do what you want.

And maybe you should split the IF ... ELSEIF ... END IF otherwise if you have both issues it will only highlight type 1 errors.

Please Login or Register  to view this content.

Regards, TMS

**mk3ll00** · 05-31-2013, 01:18 PM

whoops, right you are. I didn't notice it was set to column A instead of AA. Thank you very much for the help

**TMS** · 05-31-2013, 01:27 PM

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.