Excel Macro Advice - MsgBox Value

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

CWDENVER

Thread Starter
Joined
Dec 1, 2006
Messages
38
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?
 
Joined
Oct 21, 2004
Messages
126
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

Thread Starter
Joined
Dec 1, 2006
Messages
38
ddockstader said:
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.
 
Joined
Oct 21, 2004
Messages
126
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.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top