[CWDENVER]

I am trying to generate a Message Box with a changing value in the text that references a cell value. For example, if there are 2 errors on a worksheet (reflected by a "2" in cell A1), I want the MsgBox to say "You have 2 errors on this page". And if there are 12 errors (with a "12" already in cell A1), "You have 12 errors on this page". Can someone help me please?

 

[ddockstader]

Again, assuming that the number of errors is in cell A1

Sub Macro1()

MsgBox ("You have " & Range("A1").Value & " errors on this page.")

End Sub

 

[CWDENVER]

Again, assuming that the number of errors is in cell A1

Sub Macro1()

MsgBox ("You have " & Range("A1").Value & " errors on this page.")

End Sub

Thanks that is exactly what I needed. Now for one more trick. I have a column that will have values changing but not in consecutive order. For example if I had range A1:A10, only A1, A4, and A9 would have values, but the rest of the cells would be blank. I need to be able to generate A1, A4, and A9 into the next column consecutively as B1, B2, and B3. The A column values could change (including the number of values) and I need the B column to react accordingly. So the next time I run the data, I could have A2, A3, A4, A6, and A10 with data in them- so I need column B to be able to react and put the values (in order) as B1, B2, B3, B4, and B5. Oh and I need all this done in a MsgBox. Can you help? Thanks so much.

 

[ddockstader]

You must have me confused with someone who actually knows what they are doing! That being said, I do have an EXCEEDINGLY complicated array formula which I borrowed from some true genious a number of years ago (the exact source being long lost in my failing memory) that does something like what you want. If you are unfamiliar with array formulae, you would be well advised to look it up in Microsoft Help. When that further confuses you, the only thing you really need to know is that it works like a regular formula, except on a group of cells where you would normally specify a single cell. And to get it to work, you have to type in the formula and then press CTRL+SHIFT+ENTER (all at the same time) when it is entered correctly. Typing just ENTER guarantees that the formula won't work. I have attached a worksheet that has what you want. Enter anything you want in A1:A10 and it will appear in the same order in B1:B10, minus any holes or blanks. Now, if you try to figure out the array formulae in B1:B10, I can almost guarantee that you will qualify for the State Home for the Mentally Bewildered before you figure it out. But it works, so don't argue with success. You can copy it and (carefully) put it in another worksheet, substituting the appropriate ranges for A1:A10 and B1:B10. Then just hit CTRL+SHIFT+ENTER and you have your solution. Now, the Msgbox part is left as an exercise for the interested student. Good luck.

 

[CWDENVER]

Haha. Thanks anyway.

 

[Zack Barresse]

Continued here: http://forums.techguy.org/business-...ro-advice-data-consolidation.html#post4369975