Solved

Sorting a sheet

Posted on 2011-03-24
15
252 Views
Last Modified: 2012-05-11
Further to the related question,  i have a slight problem with the code that i am using that i would like to change.

The code below uses h3 which is the top cell of the data i want to sort by but if for any reason further columns are inserted then this code isnt going to work.

Can you tell me how to update the code so that it uses the range name for h2 which is LastChange rather than using h3.


Sub y()

Application.ScreenUpdating = False

With Range("h3", Range("h3").End(xlDown))
    .Offset(, 1).EntireColumn.Insert
    .Offset(, 1).Formula = "=IF(ISNUMBER(h3),VALUE(h3),LOOKUP(h3,{""CLOSED"",""n/a""},{1,2}))"
    .Resize(, 2).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo
    .Offset(, 1).EntireColumn.Delete
End With

Application.ScreenUpdating = True

End Sub


0
Comment
Question by:GordonMasson
  • 7
  • 5
  • 3
15 Comments
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35206821
Change Range("h3") to the Range("LastChange") and it should do the trick....
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35206827

Probably like this actually, if you could post a workbook i could validate

With Range(Range("LastRange"), Range("LastRange").End(xlDown))

-brandon
0
 
LVL 9

Expert Comment

by:McOz
ID: 35206833
Use this:
Sub y()

Application.ScreenUpdating = False

With Range("LastChange", Range("LastChange").End(xlDown))
    .Offset(, 1).EntireColumn.Insert
    .Offset(, 1).Formula = "=IF(ISNUMBER(LastChange),VALUE(LastChange),LOOKUP(LastChange,{""CLOSED"",""n/a""},{1,2}))"
    .Resize(, 2).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo
    .Offset(, 1).EntireColumn.Delete
End With

Application.ScreenUpdating = True

End Sub

Open in new window

Cheers
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35206892
yea i messed up on mine a lil bit, i put an extra Range() in there

should look as such:

With Range("LastChange", Range("LastChange").End(xlDown))
0
 

Author Comment

by:GordonMasson
ID: 35207164
Thanks...
Something not quire right here tho....
this line:
    .Offset(, 1).Formula = "=IF(ISNUMBER(LastChange),VALUE(LastChange),LOOKUP(LastChange,{""CLOSED"",""n/a""},{1,2}))"
 doesnt seem to do the same as
.Offset(, 1).Formula = "=IF(ISNUMBER(h3),VALUE(h3),LOOKUP(h3,{""CLOSED"",""n/a""},{1,2}))"

does it have to be set up as Range("LastChange") or anything like that?
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207257
Assuming "LastChange" resolves to H3, it should do the same thing. The difference is that "Range("LastChange")" is needed in VBA to refer to the range object, but within the Formula you do not need to use "Range". You just use "LastChange" as you would if you were typing the function in the cell.

Hope this helps
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207303
Gordon, can you tell me how the results from the version using the named range are different?

possibly LastChange no longer refers to H3? Since I don't know how you have set up the named range its hard to tell...
0
 

Author Comment

by:GordonMasson
ID: 35207447
Hi McOz

LastChange is actualy the header for the column so its H2
When i use the updated code all the dates in the inserted column change to 01 January 1900
When i use the original code the dates in the inserted column are kept as the original dates and the other values change to 01 January 1900

If you look at the sample worksheet here http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26893797.html
you will see how its meant to work
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207640
OK, sorry I did not take the time to see what the code was really doing before!
Try this, using formulaR1C1 to use relative references:
Sub y() 
 
Application.ScreenUpdating = False 
 
With Range("LastChange", Range("LastChange").End(xlDown)) 
    .Offset(, 1).EntireColumn.Insert 
    .Offset(, 1).FormulaR1C1 = "=IF(ISNUMBER(RC[-1]),VALUE(RC[-1]),LOOKUP(RC[-1],{""CLOSED"",""n/a""},{1,2}))" 
    .Resize(, 2).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo 
    .Offset(, 1).EntireColumn.Delete 
End With 
 
Application.ScreenUpdating = True 
 
End Sub

Open in new window


So the formulas in the new column refer to the corresponding cells in the previous column.
0
 

Author Comment

by:GordonMasson
ID: 35207737
Ah thats it McOz

That works now thanks.
Just one thing i have realised that i should have included in the earlier question.......
This is sorting column H but it should really sort all the data from cell A3...(Cell A2 is caled UnitNumber) for all rows/columns in use.
can i add a simple change to this code to sort all the active cells or should i ask a new question?

Thanks
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207864
Without testing, I think you could just change it to the following to sort all columns:
Sub y()  
  
Application.ScreenUpdating = False  
  
With Range("LastChange", Range("LastChange").End(xlDown))  
    .Offset(, 1).EntireColumn.Insert  
    .Offset(, 1).FormulaR1C1 = "=IF(ISNUMBER(RC[-1]),VALUE(RC[-1]),LOOKUP(RC[-1],{""CLOSED"",""n/a""},{1,2}))"  
    .Offset(,-(.Column - 1)).Resize(, (.Column + 1)).sort key1:=Range("i1"), order1:=xlDescending, Header:=xlNo  
    .Offset(, 1).EntireColumn.Delete  
End With  
  
Application.ScreenUpdating = True  
  
End Sub

Open in new window

Give it a shot!
0
 
LVL 9

Expert Comment

by:McOz
ID: 35207915
Oh, and to make the sort key relative as well, change this:
key1:=Range("i1"),

Open in new window

to this:
key1:=Range("LastChange").Offset(,1),

Open in new window

Cheers
0
 

Author Comment

by:GordonMasson
ID: 35208253
Very close McOz but its sorting the header row...row 2 as well so i am loosing my headers

Any thoughts?

Thanks again for your help
0
 
LVL 9

Accepted Solution

by:
McOz earned 500 total points
ID: 35208333
Yes! change Header:=xlNo to Header:=xlYes in the sort line

Good luck!
0
 

Author Closing Comment

by:GordonMasson
ID: 35431369
Thanks for your help and sorry for the delay in closing this out
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question