Posts by corkyo4

    Is there a way in which you can create an if function or similar to give a reason for why the cell of data has an error.


    Basically i have some data in a table, and when a #num! error pops up id like to in the cell next to it give a sentence stating why this has happened.


    E.G.


    Cell A1 Cell B1
    #Num! Number error due to 'x' being too small



    Not sure how i can get this to work, i have tried a basic if function but that hasnt worked.

    I have 2 worksheets named,


    inner leaf


    outer leaf


    id like to place a button on the inner leaf worksheet that will print both of these worksheets once clicked.


    Anyone know the vba code to be able to do this???



    Many thanks

    Re: Macro To Run On Sheets Without Selection


    Copied it in to excel and got an error with regards to the part of code at the bottom (End Select) so i changed it to (End With) and got an error with


    Sheet11.Cells.Selection.ClearContents


    error was, runtime error 438


    "object doesnt support this property or method"

    Is there any way this code (below) can be modified so that the macro runs without actually selecting the sheet it requires. The reason for this is so i can hide the sheet (Column Output 3) from view so the data isnt displayed. at the moment the sheet is on view and i would rather it werent.


    this is the code i have


    i have this code and need it to sort a specified sheet but have been told that the code i have wil only sort the active sheet. how do i modify it to sort the sheet is want??


    the sheet to sort is called beam output 3


    Code
    Range("A1:H167").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

    Re: Checking A Value That Is A Multiple Of Another Value


    Thanks guys that works a treat, incidentally changing the subject slightly,
    can anyone tell me how i can make this VBA code that sorts a sheet, sort a specific sheet rather than the active one..


    Code
    Range("A1:H167").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal


    ideally i would like it to sort data in a sheet named, beam output 3


    thanks

    Is there a way to check a value, that is a multiple of another value.


    E.G.



    say the user specifies a height of 225 and then specify laminations of 45


    is there a way to check the value of 225 to ensure 45 divids into it perfectly??


    and if it doesnt then gives an error message of some kind.


    So if someone specifies height of 200 and lamination of 45 an error would show because 45 cant be divided into 200 perfectly.

    Re: Macro To Sort Then Copy



    anyone know how i can use the .dot qualifier in front of range to make this sort code work with a specified sheet?????

    Re: Macro To Sort Then Copy


    ah right, i get u.


    is there any way i can get it to sort another sheet???


    i spose i have to record a macro that selects the sheet first then sorts then selects the previous sheet.

    Re: Macro To Sort Then Copy


    the setup i have is this.


    an input sheet where the user specifies certain criteria,


    i have a (1st) output sheet where other data is calculated.


    i then have a 2nd output sheet where there are IF functions which act as checks. These IF statements relate to data in the first output sheet.


    the 2nd output sheet is the one that is filtered and copied to a 3rd output sheet where i would like to sort the data.


    the only merged cells i have are in the first output sheet and the input.

    Re: Macro To Sort Then Copy


    I dont have any merged cells in my data, sorting the cells manually works fine, but recording a macro to do it, and it doesnt work ?????? :(


    i can send you a sample worksheet but it may take time.

    Re: Macro To Sort Then Copy


    ok thanks, that works now, but i have one final problem, sorry to be a pain.


    but i have this piece of code to sort the data, and i need to combine it with the advancedfilter code, how do i do it


    this is the sorting code

    Code
    Range("A1:H167").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal


    this is the filter code


    i need the sort code to execute after the filter code


    ive tried this



    but i get a runtime error saying, the operation requires the merged cells to be identically sized. not sure what this means

    Re: Macro To Sort Then Copy


    Right, i have managed to get the advanced filter method working, and its just what i need. However rather than create a new worksheet to paste data into, can it paste it into an existing one named


    Beam Output 3


    is this possible? if so how can i do it.


    this is the code i have


    Re: Macro To Sort Then Copy


    yeah that worked, but when i tried it on my own sheet i couldnt get it to work. so i used the other method instead, but of course that doesnt work either so im stuck.



    i just need to filter cells that contain if functions basically.


    then once they are filtered i can use a macro to sort them and then extract the data into my output.

    Re: Macro To Sort Then Copy


    The method above with the auto filter function doesnt work, either.



    Seems i have use the looping idea, just needs to be modified to allow cells with formulas to be filtered