1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro Advice - MsgBox Value

Discussion in 'Business Applications' started by CWDENVER, Jan 18, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. CWDENVER

    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?
     
  2. ddockstader

    ddockstader

    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
     
  3. CWDENVER

    CWDENVER Thread Starter

    Joined:
    Dec 1, 2006
    Messages:
    38
    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.
     
  4. ddockstader

    ddockstader

    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.
     

    Attached Files:

  5. CWDENVER

    CWDENVER Thread Starter

    Joined:
    Dec 1, 2006
    Messages:
    38
    Haha. Thanks anyway.
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/536336

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice