+ Reply to Thread
Results 1 to 3 of 3

Nee macro to check values in each cell & give error message if the values crosses a number

  1. #1
    Registered User
    Join Date
    08-22-2015
    Location
    india
    MS-Off Ver
    2010
    Posts
    2

    Nee macro to check values in each cell & give error message if the values crosses a number

    Hi all,
    Here's the table i am using:

    A B C D E F G H I J K L
    1 EMP NO 1-Sept 2-Sept 3-Sept 4-Sept 5-Sept 6-Sept Count of N Count of A Count of G
    2 N N N A A G G N 4 2 2
    3 N N A A A G A G 2 4 2
    4 N N A A A G A G 2 4 2
    5 N N A A A G A G 2 4 2



    Columns J,K and L are the counts of Ns, As and Gs. Have used Count excel formula.

    So my problem is this. I need to write a macro to check each value from J2 to L4. If the value in any of the cell in the range J2 to L4 goes beyond 3 it should give an error pop-up saying -"Error-Too many values". Note that the values in J2 to J4 keep changing when the values f N, A, and G changes in A1 to H4.

    if i am entering, lets say N in row 1, if i give 3 Ns it shouldn't give me error. But the 4th N entry, once i enter it should give a pop up error. It can let me enter more than 3 Ns but every-time it crosses 3 it shoud give pop up.
    Ex: Enter 1 N - no pop up
    Enter 2nd N - no pop up
    Enter 3rd N -no pop up
    Enter 4th N - Pop up
    Enter 5th N- Pop up
    Enter 6th N - Pop up .

    let me know the macro for this. As a noob i didnt get any proper solution for this. All i wrote was this. But its not of any use.

    Option Explicit
    Sub Abi()

    Dim i As Range
    For Each i In Range("J2:L4")
    If i.Value > 2 Then
    MsgBox "hello"
    End If
    Next i
    End Sub

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,482

    Re: Nee macro to check values in each cell & give error message if the values crosses a nu

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    To add a file to a new post

    To add a file to an existing post.

    Add Code Tags to VBA Code
    AA CodeTags.jpg

  3. #3
    Registered User
    Join Date
    08-22-2015
    Location
    india
    MS-Off Ver
    2010
    Posts
    2

    Re: Nee macro to check values in each cell & give error message if the values crosses a nu

    Hey davesexcel , Thanks for the reply. And sorry for the incomplete data.

    I have attached the sheet which i have prepared. Also the things which needs to be done in there.

    Basically its like this. For a particular employee, the max allowed shifts[N, A or G] are 3. If somebody tries to give him more than 3 shifts for a week, it should give an error pop-up saying "the maximum limit has crossed".

    Let me know if any more clarity is needed.Trial.xlsm

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to give the count of unique values after comparing the comma separated values
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-03-2014, 12:41 AM
  2. [SOLVED] Macro to Create Message box based on cell values
    By aneshdas in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 12-20-2013, 05:24 PM
  3. [SOLVED] Compare cell values of different Workbooks and prompt message using macro
    By jericlapuz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2012, 03:42 AM
  4. a macro to check cell values against other cell values
    By ncarrocino in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2012, 12:59 PM
  5. Look at last pasted cell and if the same give me an error message box
    By jharaldson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2011, 08:05 PM
  6. how to compare multiple values and give error
    By krish T in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-16-2010, 10:57 PM
  7. Give Error Message if A1 is not a number or "Knt"
    By krish T in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-14-2010, 12:53 PM
  8. Replies: 2
    Last Post: 01-02-2010, 08:23 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1