Posts by aisietie

    Re: Check if data in columns is complete

    Thanks to both,

    I put the code in two buttons, Button 1 is pike's code, button 2 is nilem's code.
    The logic that I wants is that when I click a button in a column, the messagebox returns the missing items that are in the list sheet.

    With pike's code, the return is only one missing item, with nilem's code, the answer is right, but now I must split the code, so one button controls only one column.

    But already a great thanks to both,


    I need a code that:
    When a workbook is active :
    1. Open an other workbook (\\Belg\VoorbeeldAA.xlsm) -ignore macro's
    2. Copy range A1:S60 from sheet ("Test") from this workbook
    3. Close workbook VoorbeeldAA.xls without saving
    4. Paste the sheet as new sheet ("AA") in the active workbook
    5. Open workbook (\\Belg\VoorbeeldBB.xlsm) -ignore macro's
    6. Copy range A1:S60 fromsheet ("Test") from this workbook
    7. Close workbook VoorbeeldBB.xls without saving
    8. Paste the sheet as new sheet ("BB") in the active workbook

    I tried it with the macro recorder, but the code:

    Workbooks.Open FileName:= _

    Gives me problems renaming the pasted sheets, and to deactivate the clipboard warning, and save changes warnings.

    Thanks in advance,


    Re: If Sum Exceeds Certain Value Display Message Else Sum Values

    Thanks again for the reply,

    but I think this code will only check cell A1 in the sheets.
    This is not enough.
    I have in sheet 1 numbers in the range A1:D1000, also in sheet 2 numbers in the range A1:D1000
    In the total sheet I have the same range A1:D1000 with in each cell in the range, the sum of the corresponding cells in the other sheets.
    Here I want that the sum is max 20.
    So when in sheet 1 in cell D5 the value is "19" and I put "2" in cell D5 in sheet 2, a messagebox pop-up with the warning that in sheet "total" the limit is reached.

    Hope this is possible,


    Re: Control the sum of values in a cell

    Thx for the quick reply, but I want to be warned in the sheet I am working.
    So, when the sheet halo is the active sheet, and I enter a value in a cell in the sheet halo, and the result is that the SUM in sheet total is reached, a messagebox give me a warning.

    It have it working now with a function

    Function IfMsg(Test, ifTrue)
        If Test = True Then
            IfMsg = ifTrue
            IfMsg = "Te Groot"
            MsgBox "Totaal is te groot" & vbCr & "Maximum is overschreden"
               End If
    End Function

    and than in each cell of the sheet total:
    a formula


    but this slows the calculation because the formule is in more than 5000 cells, and every time a cell is changed, it controlls all the cells if not the value is to high.

    It is the slowness of the function that is the problem, and because I am searching for an alternative.



    I have on sheet(total) in each cell the formule sum of values in corresponding cells in other sheets.
    Something like =SUM(test!A1;rapport!A1;halo!A1)
    I want to allow a max in each cell in sheet total let us say max = "20"
    Is it possible to give a warning (messagebox) when for instance
    Sheet test cell A1 has value "19"
    In sheet halo in cell A1 "2" is inputted, and then a messagebox pop-up "value in Sum in sheet Total is to big".
    because the SUM is more than "20"

    Is it possible?
    Can somebody give advise?



    I use the code to copy the first 3 columns, starting at row 9 to the last used row.

    Sheets("Test").Range("A9:C9", Sheets("Test").Range("A9").End(xlDown)).Copy

    No problem when there is more than 1 row, but when the range contains only 1 row, and sometimes this happens, the code copies to the last row of the sheet, the xlDown is not recognized.

    What is there to do, so the code works, no matter how many rows there are?




    I try to show the value in row A.

    With the code

    I want that the cell value in rowA is shown, but the code returns the first non blanc cell above the found range in the column, and not the top cell (on RowA)in the column.
    What is "stronger" then End(xlUp), and goes to the top of the column?


    Re: Paste row in first free row in Range.

    Thanks for the quick reply,
    but I want to paste the row in the first free row in the range between A101 and A200 starting in A101
    It is possible that the first row 101 is emty, and then the row must be pasted in row 101