macro to check data against different ranges on different worksheets

  • I have a workbook with 4 different worksheets.Depending on the data entered into the “userform”, one of this worksheets is opened and the data is copied into the corresponding cell. This data are expected to be within a certain range and this range is different for each sheet. In some worksheets, there are two different ranges. I am trying to make a macro, which will check the copied values against the defined range(s) on these worksheets (the last entered data) and pop-up a message. These cells to be checked and the ranges against which the entered data will be checked are:- Column "K", ranges are: - 1200 to 1500 and 1200 to 1800 in S50MC worksheet - 1500 to 1750 in S60MC worksheet and - 2250 to 2600 and 2250 to 2800 in S70MC worksheetIf the value entered into the K column cell of the last row is within these ranges, then the value entered in the L column is checked. The ranges are: - S50MC, 81 - 91 - S60MC, 115 - 127 - S70MC, 105 - 117If this condition holds too, last thing is to check column O. The value in this cell should be - S50MC, 523, 524 and 525 - S60MC, 623, 624 and 625 - S70MC, 723, 724 and 725If all of these conditions hold, there pops a userform with a mesage where it says "There might be an AM for this engine. Do you want to survey the engine performance?" If the user clicks the "yes" button, it opens another excel, if the answer is no, the userform is closed.The same information can also be found in the attached file.However, I have one big problem. I have no clue about how to do all these things?! Any suggestions?Thank you in advance,Selc

  • Re: macro to check data against different ranges on different worksheets


    Your post lacks sufficient detail required to provide a solution.


    To get the most precise answers, it is best to upload a sample workbook (sensitive data scrubbed/removed) that shows a few manually created examples of the desired results.
    The structure and data types of the workbook must exactly match that of the real workbook. Include in the workbook a clear and explicit explanation of all requirements.The sample workbook only needs to contain enough data to illustrate the need to aid with developing the solution.


    [COLOR="navy"]How to edit your post or thread title or upload an attachment[/COLOR]


    1. Click the EDIT POST in the gray band immediately below your post
    2. Click Go Advanced
    3. Edit the post or thread title
    4. To upload: scroll down to Manage Attachments -- use ZIP compression if necessary to meet file size limitations

  • Re: macro to check data against different ranges on different worksheets


    Hej Brown,First of all, thank you very much for the answer.I have pasted the code you have sent to me in the conventional's user form VBA code space, under the "Unload me" command. But, there happens nothing when I run the macro? Is there anyting else I should do to make it work?

  • Re: macro to check data against different ranges on different worksheets


    selc






    1) How are you entering the data in col.K?
    If through the userform, where is the corresponding textbox?
    2) What do yo mean by "Column "K", ranges are: - 1200 to 1500 and 1200 to 1800 in S50MC worksheet"
    Does it mean just 1200 through 1800 straight?

  • Re: macro to check data against different ranges on different worksheets


    Hej,The data is entered through the "conventional" userform (with the command Range ("O" &... = Textbox1.Text)And yes there are 2 different ranges - if the value is between 1200 and 1500, there are two possible options for the subject engine, however if it is over 1500 and still smaller than 1800, then there is only one option.hope it is a bit less confusing now!Selcuk

  • Re: macro to check data against different ranges on different worksheets


    Still confusing...




    Are we talking about the validation rules on Col.O or Col.K?
    Textbox on Conventional userform seems to transfer the data in textox1 to Col.O...


    Can you just tell me exactly what you want to do on this userform ?

  • Re: macro to check data against different ranges on different worksheets


    Sorry, you are right - Column K is not entered through the userform. It is the power (column J, entered through the userform "engine data"), divided by the value of the "cylinder number" given in the userform "engine type". That data is copied into the last XFD2. In the column K, there is a formula (basically col.J/col.XFD2). So we are talking about column K here.But when we are done entering data into all the userforms, I want the newest macro to check the data of the K, L and O against the ranges I have given in the file I uploaded. And these ranges are different for each worksheet and there are 2 different ranges in two of these worksheets.Was a bit better maybe?

  • Re: macro to check data against different ranges on different worksheets


    Still not clear though...


    And you just want popup message telling like "No", when it is out of range ?


    Or better when they entered invalid value in the corresponding textbox? No need?





    P.S
    To those who has trouble posting code.


    Go to Settings - General Settings - Miscellaneous Options
    then choose
    "Standard Editor - Extra formatting controls",
    not
    "Enhanced Interface - Full WYSIWYG Editing"

  • Re: macro to check data against different ranges on different worksheets


    If the entered values are invalid, I want a message to pop-up and ask (for example if it is after the code checks the column K) "There is no AM for this engine due to the... Do you stil want to continue." Yes or No question. If it is a yes, the data is entered, saved and the worksheet "start" opens again. If the answer is yes, the macro checks the next condition, the column L. And if all the conditions are met, then a message box opens ad says, "there may be an AM for this engine.... Do you want to..." If the answer is yes, a new excel workbook is opened, if the answer is no, the data is saved and the worksheet "start" opens again.

  • Re: macro to check data against different ranges on different worksheets


    OK




    So when textbox6(Col.J) entered, it can be calculated if the value entered is valid or not valid. I mean within the range or not.
    Is that correct ?

  • Re: macro to check data against different ranges on different worksheets


    Yes (you mean conditional formatting I assume). Otherwise, there must be a way to do it also by macro (which I dont know yet how).

  • Re: macro to check data against different ranges on different worksheets


    This is only the case for S50MC sheet


    Add the code onto EngineData form and enter something invalid number in textbox 6.




  • Re: macro to check data against different ranges on different worksheets


    Now we are almost there... perfect.
    But, would it be possible to continue entering data, although they are out of range and get this message after the "conventional" form is also filled in all entered data is saved?


    By the way thank you very much for your help and I do apologize if I am causing you trouble.

  • Re: macro to check data against different ranges on different worksheets


    OK,


    That was only for the testing....



    Now you got the idea of what we are doing huh ?


    If you want warn the user, it is better to do it on the right time, that how I think.

  • Re: macro to check data against different ranges on different worksheets


    Yep, actually I did.
    But on the other hand, I want use this excel-file also as a database, will be used by three and there is a risk that different people are checking for the same engine. Thats because I want everything entered, regardless og meeting the conditions or not, to be saved. Otherwise it would be, like you suggested, much more convenient to stop entering the data for the engine which is not fulfilling the requirements.

  • Re: macro to check data against different ranges on different worksheets


    When it is a no - then all the entered data is saved and the user is sent back to the first worksheet "Start".

  • Re: macro to check data against different ranges on different worksheets


    Do you mean, continue anyway even if No is selected?


    In that case, will the message box with just OK button with the waring message do the job?

  • Re: macro to check data against different ranges on different worksheets


    Yes actually it is an "OK" button so that the user continues entering data. End then, when the last userform is filled in, which is the "conventional", the message box will pop-up and warn the user, if there is any data which do not meet the conditons. For example, "the power is not within the defined ranges", and/or "the engine speed is not within the specified limit" and/or "The valve is not covered by any AM". Then the user clicks the open button and the data is saved and the user is sent back to the main worksheet.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!