Posts by corkyo4

Re: Function To Give Reason for cell error

Well i would do, but the solution you gave works just fine.

Thanks

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.

Re: Button To Print Worksheets

Thanks for that

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

thats the same???

or do you mean delete it??

if you meant delete it, i get an error as it tries to sort the active sheet which isnt the sheet i need sorting.

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

Sub DistributeRows()


    Sheet11.Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.ClearContents


Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long


    Set wsAll = Worksheets("Column Output 2")
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    Set wsCrit = Worksheets.Add
    Set wsNew = Worksheets("Column Output 3")
    
    Set rngCrit = wsCrit.Range("A1:A2")
    rngCrit(1) = "Check"
    rngCrit(2) = "OK"
    
    wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=wsNew.Range("A1"), Unique:=False
   
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
    Sheets("Column Output 3").Select
    Range("A1:H167").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Sheets("Column").Select
  
End Sub

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

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

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

Quote from norie

Oh no, merged cells.:nono: :rambo: :rambo:

Do you really need them?

Which sheet are they actually on?

This code will sort the active sheet.

Code

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

To sort the sheet in the With statement you need the . dot qualifier in front of Range.

Are you sure the extracted data isn't sorted anyway?

PS Could you attach a sample workbook?

Display More

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

yeah i did but i dont no how to use it, my VBA knowledge is none existent really so i have to be spoon fed

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

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

this is the filter code

Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long


    Set wsAll = Worksheets("Column Output 2")
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    Set wsCrit = Worksheets.Add
    Set wsNew = Worksheets("Column Output 3")
    
    Set rngCrit = wsCrit.Range("A1:A2")
    rngCrit(1) = "Check"
    rngCrit(2) = "OK"
    
    wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=wsNew.Range("A1"), Unique:=False
   
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub

i need the sort code to execute after the filter code

ive tried this

Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long


    Set wsAll = Worksheets("Column Output 2")
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    Set wsCrit = Worksheets.Add
    Set wsNew = Worksheets("Column Output 3")
    
    Set rngCrit = wsCrit.Range("A1:A2")
    rngCrit(1) = "Check"
    rngCrit(2) = "OK"
    
    wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=wsNew.Range("A1"), Unique:=False
   
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
With Worksheets("Column Output 3")
    Range("A1:H167").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End With
    
End Sub

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

Sub DistributeRows()
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long


    Set wsAll = Worksheets("Beam Output 2")
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
    Set wsCrit = Worksheets.Add
    Set wsNew = Worksheets.Add
    
    Set rngCrit = wsCrit.Range("A1:A2")
    rngCrit(1) = "Final check"
    rngCrit(2) = "OK"
    
    wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=wsNew.Range("A1"), Unique:=False
   
    Application.DisplayAlerts = False
    wsCrit.Delete
    Application.DisplayAlerts = True
    
End Sub

Re: Macro To Sort Then Copy

what do you mean by paste special values??

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