Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sorting a sheet

Posted on 2011-03-24
15
Medium Priority
?
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

636 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