-
Feb 25th, 2017, 02:28 PM
#1
Thread Starter
Member
[RESOLVED] Linking cells in Excel?
Greetings all,
I have a set of data which is two columns; the left columns is a series of names and the right column a series of values. The issue I've encountered is that I need to sort the values into size order, but I also need the name to remain next to the value it represents.
If I haven't explained sufficiently, imagine I have a table of names and heights, I need to sort the heights from lowest to highest, but the names must also be rearranged to remain next to their respective height.
I have been thinking on this for a while and I can't figure anything out. If anyone has an idea, I'd really appreciate hearing it!
Thanks!
P.S. Sorry for bad title. Wasn't sure how to summarise this haha.
-
Feb 25th, 2017, 05:00 PM
#2
Re: Linking cells in Excel?
you just sort the range of 2 columns, by the second column
are you trying to do this by code?
try like
Code:
range("a1:b99").sort range("b1")
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 26th, 2017, 08:44 AM
#3
Thread Starter
Member
Re: Linking cells in Excel?
Originally Posted by westconn1
are you trying to do this by code?
I am, although I can't actually figure out the syntax to sort numbers in ascending order. Three different sources have given me different syntax, and all three have led to different errors...
Any chance you know the correct syntax to sort a range of cells in ascending order?
I thought it would just be as simple as: myRange.Sort Order1:=xlAscending. But it just returns and object required error.
-
Feb 26th, 2017, 03:16 PM
#4
Re: Linking cells in Excel?
have you set myrange to a valid range object?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 26th, 2017, 03:53 PM
#5
Thread Starter
Member
Re: Linking cells in Excel?
I simply used:
Dim myRange as Range
myRange = Cells("A1:A100")
Is this incorrect procedure?
-
Feb 26th, 2017, 09:05 PM
#6
Re: Linking cells in Excel?
Try like so:
Code:
Sub sortRange()
Dim ws As Worksheet
Dim myRange As Range
Set ws = ActiveSheet
Set myRange = ws.Range("a1:b100")
myRange.Sort key1:=ws.Range("b1"), Order1:=xlAscending
End Sub
-
Feb 27th, 2017, 01:36 AM
#7
Re: Linking cells in Excel?
Dim myRange as Range
myRange = Cells("A1:A100")
this should have caused an error, as you need the set keyword for range objects, as in bryces example, also you need to include all columns that have to be reordered
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 28th, 2017, 07:27 AM
#8
Thread Starter
Member
Re: Linking cells in Excel?
Originally Posted by vbfbryce
Try like so:
Code:
Sub sortRange()
Dim ws As Worksheet
Dim myRange As Range
Set ws = ActiveSheet
Set myRange = ws.Range("a1:b100")
myRange.Sort key1:=ws.Range("b1"), Order1:=xlAscending
End Sub
You guys have been a great deal of help.
Is there any chance I could ask you to explain the syntax of the Sort method? The code you've given me works exactly as intended, but I have no idea why/how it works. I've read the method syntax and descriptions on MSDN, but that hasn't really helped.
Again, cheers for your help!
-
Feb 28th, 2017, 07:28 AM
#9
Thread Starter
Member
Re: Linking cells in Excel?
Btw, if I mark the thread as resolved, does it lock the thread? Or am I OK to mark it as resolved now seeing as my original question has been answered?
-
Feb 28th, 2017, 08:38 AM
#10
Re: Linking cells in Excel?
The syntax is similar to when you sort manually in a worksheet. The arguments are first "key" and "sort order," allowing for up to 3 keys (columns to be sorted by). Then you can specify whether or not the columns have headers, and a few other settings that I generally don't change from the default.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|