i want to write a macro
that would contain a do loop
and within that do loop i want to do sorting

can any one help i cant understand of how to select the cells that i
want to sort in VBA language

hope i have made my self clear


regards
indraneel

Try using the Macro Recorder.........Tools Macros Record new Macro, while
you go ahead and perform the sort you want by hand one time. The Recorder
will make a macro as you go.........then you can do ALT-F11 and view/edit it.
Post back if you run into trouble.

hth
Vaya con Dios,
Chuck, CABGx3





"indraneel" wrote:

i want to write a macro
that would contain a do loop
and within that do loop i want to do sorting

can any one help i cant understand of how to select the cells that i
want to sort in VBA language

hope i have made my self clear


regards
indraneel

hi clr


i did that

and i can and have developed a macro for 10 sets of values

but i have data that is 15 pages long in every analyses
so i want to make a macro with a do loop and within it want a sort (are
u understanding me or is it confusing:)) as i want to standardize the
procedure.


regards
indraneel
CLR wrote:
Try using the Macro Recorder.........Tools Macros Record new Macro, while
you go ahead and perform the sort you want by hand one time. The Recorder
will make a macro as you go.........then you can do ALT-F11 and view/edit it.
Post back if you run into trouble.

hth
Vaya con Dios,
Chuck, CABGx3





"indraneel" wrote:

i want to write a macro
that would contain a do loop
and within that do loop i want to do sorting

can any one help i cant understand of how to select the cells that i
want to sort in VBA language

hope i have made my self clear


regards
indraneel

Something like the following will work
The first thing to do is to name the ranges you want sorted, then
build these names into the macro. In the example below, three ranges
"MyRange1", "MyRange2", "MyRange3"

then set the For..Next loop counter accordingly .


Sub SortRanges()
Dim rArray
Dim x As Integer
rArray = Array("MyRange1", "Myrange2", "Myrange3")

For x = 0 To 2
Range(rArray(x)).Sort key1:=Range(rArray(x)).Columns(1),
order1:=xlAscending
Next
End Sub



On 11 Sep 2006 10:09:54 -0700, "indraneel"
wrote:

i want to write a macro
that would contain a do loop
and within that do loop i want to do sorting

can any one help i cant understand of how to select the cells that i
want to sort in VBA language

hope i have made my self clear


regards
indraneel

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

yes richard that a good idea but the problem with that is that i have
the problem that looks like this


I have a set of data that i get from analyses


it is a 15 PAGE DATA that is pasted in excel


each page contains 10 SETS OF ENTRIES in 1 column say EID element id so
doing range method would be a hard going.and also i want to standardize
the method so......


the data would look like
COLUMNS:
EID A B C D E F G H I J K
123456
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2
23564
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2


AND SO ON


ok now what i want to do is sort by column K say(Descending) then want
the highest value to be displayed with the Element Id
so that i can move them to a new sheet where i can again sort the same
for say col K again


i was able to do it for 1 Page that is 10 values but i used the Record
Macro button but i dont see it as a good way to go and so wanted to see

how can i do it using do or if command and within it a sort command
that selects the on screen value


thank you in advance


regards
indraneel



Richard Buttrey wrote:
Something like the following will work
The first thing to do is to name the ranges you want sorted, then
build these names into the macro. In the example below, three ranges
"MyRange1", "MyRange2", "MyRange3"

then set the For..Next loop counter accordingly .


Sub SortRanges()
Dim rArray
Dim x As Integer
rArray = Array("MyRange1", "Myrange2", "Myrange3")

For x = 0 To 2
Range(rArray(x)).Sort key1:=Range(rArray(x)).Columns(1),
order1:=xlAscending
Next
End Sub



On 11 Sep 2006 10:09:54 -0700, "indraneel"
wrote:

i want to write a macro
that would contain a do loop
and within that do loop i want to do sorting

can any one help i cant understand of how to select the cells that i
want to sort in VBA language

hope i have made my self clear


regards
indraneel

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

I think that CLR's idea was not that you record a macro that does it for you
every time, but to use the code it generates as a guide to how to begin to
build your loops, to see how Excel makes references to things and even see
the general format of the code to do the sorting.

You kind of have to figure out what's going to control your Do...Loop
structure and how to define the address ranges that the Sort command is going
to need to do the sort, and you could even automate all of that AND the copy
and paste to other sheets. You just have to sit down and think it all out
and possibly come back for help/hints in this forum from time to time.

"indraneel" wrote:

yes richard that a good idea but the problem with that is that i have
the problem that looks like this


I have a set of data that i get from analyses


it is a 15 PAGE DATA that is pasted in excel


each page contains 10 SETS OF ENTRIES in 1 column say EID element id so
doing range method would be a hard going.and also i want to standardize
the method so......


the data would look like
COLUMNS:
EID A B C D E F G H I J K
123456
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2
23564
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2


AND SO ON


ok now what i want to do is sort by column K say(Descending) then want
the highest value to be displayed with the Element Id
so that i can move them to a new sheet where i can again sort the same
for say col K again


i was able to do it for 1 Page that is 10 values but i used the Record
Macro button but i dont see it as a good way to go and so wanted to see

how can i do it using do or if command and within it a sort command
that selects the on screen value


thank you in advance


regards
indraneel



Richard Buttrey wrote:
Something like the following will work
The first thing to do is to name the ranges you want sorted, then
build these names into the macro. In the example below, three ranges
"MyRange1", "MyRange2", "MyRange3"

then set the For..Next loop counter accordingly .


Sub SortRanges()
Dim rArray
Dim x As Integer
rArray = Array("MyRange1", "Myrange2", "Myrange3")

For x = 0 To 2
Range(rArray(x)).Sort key1:=Range(rArray(x)).Columns(1),
order1:=xlAscending
Next
End Sub



On 11 Sep 2006 10:09:54 -0700, "indraneel"
wrote:

i want to write a macro
that would contain a do loop
and within that do loop i want to do sorting

can any one help i cant understand of how to select the cells that i
want to sort in VBA language

hope i have made my self clear


regards
indraneel

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

hello J Latham


yes i get ehat you are trying to suggest
i am doing the same right now what i am stuck with is how to write a
sort command inside a so loop so that it does the sorting for all the
instances


regards
indraneel
JLatham wrote:
I think that CLR's idea was not that you record a macro that does it for you
every time, but to use the code it generates as a guide to how to begin to
build your loops, to see how Excel makes references to things and even see
the general format of the code to do the sorting.

You kind of have to figure out what's going to control your Do...Loop
structure and how to define the address ranges that the Sort command is going
to need to do the sort, and you could even automate all of that AND the copy
and paste to other sheets. You just have to sit down and think it all out
and possibly come back for help/hints in this forum from time to time.

"indraneel" wrote:

yes richard that a good idea but the problem with that is that i have
the problem that looks like this


I have a set of data that i get from analyses


it is a 15 PAGE DATA that is pasted in excel


each page contains 10 SETS OF ENTRIES in 1 column say EID element id so
doing range method would be a hard going.and also i want to standardize
the method so......


the data would look like
COLUMNS:
EID A B C D E F G H I J K
123456
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2
23564
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2


AND SO ON


ok now what i want to do is sort by column K say(Descending) then want
the highest value to be displayed with the Element Id
so that i can move them to a new sheet where i can again sort the same
for say col K again


i was able to do it for 1 Page that is 10 values but i used the Record
Macro button but i dont see it as a good way to go and so wanted to see

how can i do it using do or if command and within it a sort command
that selects the on screen value


thank you in advance


regards
indraneel



Richard Buttrey wrote:
Something like the following will work
The first thing to do is to name the ranges you want sorted, then
build these names into the macro. In the example below, three ranges
"MyRange1", "MyRange2", "MyRange3"

then set the For..Next loop counter accordingly .


Sub SortRanges()
Dim rArray
Dim x As Integer
rArray = Array("MyRange1", "Myrange2", "Myrange3")

For x = 0 To 2
Range(rArray(x)).Sort key1:=Range(rArray(x)).Columns(1),
order1:=xlAscending
Next
End Sub



On 11 Sep 2006 10:09:54 -0700, "indraneel"
wrote:

i want to write a macro
that would contain a do loop
and within that do loop i want to do sorting

can any one help i cant understand of how to select the cells that i
want to sort in VBA language

hope i have made my self clear


regards
indraneel

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Ok, again, primitive example. Within your loop you determine that you're at
the starting point (probably by row) and you know you want to include things
for sorting at a particular column.

So when you determine you are at a starting point, you could have a string
variable declared by a statement like
Dim anyRange As String
earlier in the routine. At the point where you know you are at the first
row to be sorted, and knowing you need first column to be column C, for
example, you can begin building the area to be sorted like this:
anyRange = "C" & Str(ActiveCell.Row)) & ":"
that assumes that you have actually got a cell on that row selected, but
somehow or other you know the row involved. Lets say it turns out to be row
4, then at this point, anyRange would look like C4:
You continue on until you find you're at the end of the rows to be sorted
and know that you need to sort everything on all of the rows from 4 to this
current one along with everything in cells C all the way to V, so you modify
anyRange using a statement similar to this:
anyRange= anyRange & "V" & Str(ActiveCell.Row))
If we were on row 407 then anyRange would now look like C4:V407
to prepare to sort, select that range with a statement like this
Range(anyRange).Select
and then execute your sort statement (which you probably can steal right out
of a recorded macro that performed a similar sort even if it was done on a
different group of data).

Does that help any?


"indraneel" wrote:

hello J Latham


yes i get ehat you are trying to suggest
i am doing the same right now what i am stuck with is how to write a
sort command inside a so loop so that it does the sorting for all the
instances


regards
indraneel
JLatham wrote:
I think that CLR's idea was not that you record a macro that does it for you
every time, but to use the code it generates as a guide to how to begin to
build your loops, to see how Excel makes references to things and even see
the general format of the code to do the sorting.

You kind of have to figure out what's going to control your Do...Loop
structure and how to define the address ranges that the Sort command is going
to need to do the sort, and you could even automate all of that AND the copy
and paste to other sheets. You just have to sit down and think it all out
and possibly come back for help/hints in this forum from time to time.

"indraneel" wrote:

yes richard that a good idea but the problem with that is that i have
the problem that looks like this


I have a set of data that i get from analyses


it is a 15 PAGE DATA that is pasted in excel


each page contains 10 SETS OF ENTRIES in 1 column say EID element id so
doing range method would be a hard going.and also i want to standardize
the method so......


the data would look like
COLUMNS:
EID A B C D E F G H I J K
123456
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2
23564
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2


AND SO ON


ok now what i want to do is sort by column K say(Descending) then want
the highest value to be displayed with the Element Id
so that i can move them to a new sheet where i can again sort the same
for say col K again


i was able to do it for 1 Page that is 10 values but i used the Record
Macro button but i dont see it as a good way to go and so wanted to see

how can i do it using do or if command and within it a sort command
that selects the on screen value


thank you in advance


regards
indraneel



Richard Buttrey wrote:
Something like the following will work
The first thing to do is to name the ranges you want sorted, then
build these names into the macro. In the example below, three ranges
"MyRange1", "MyRange2", "MyRange3"

then set the For..Next loop counter accordingly .


Sub SortRanges()
Dim rArray
Dim x As Integer
rArray = Array("MyRange1", "Myrange2", "Myrange3")

For x = 0 To 2
Range(rArray(x)).Sort key1:=Range(rArray(x)).Columns(1),
order1:=xlAscending
Next
End Sub



On 11 Sep 2006 10:09:54 -0700, "indraneel"
wrote:

i want to write a macro
that would contain a do loop
and within that do loop i want to do sorting

can any one help i cant understand of how to select the cells that i
want to sort in VBA language

hope i have made my self clear


regards
indraneel

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

May have made a typo in that next to last formula, should have been
anyRange= AnyRange & "V" & Str(ActiveCell.Row)
just one closing ), not 2 that I think I accidentally typed.

"indraneel" wrote:

hello J Latham


yes i get ehat you are trying to suggest
i am doing the same right now what i am stuck with is how to write a
sort command inside a so loop so that it does the sorting for all the
instances


regards
indraneel
JLatham wrote:
I think that CLR's idea was not that you record a macro that does it for you
every time, but to use the code it generates as a guide to how to begin to
build your loops, to see how Excel makes references to things and even see
the general format of the code to do the sorting.

You kind of have to figure out what's going to control your Do...Loop
structure and how to define the address ranges that the Sort command is going
to need to do the sort, and you could even automate all of that AND the copy
and paste to other sheets. You just have to sit down and think it all out
and possibly come back for help/hints in this forum from time to time.

"indraneel" wrote:

yes richard that a good idea but the problem with that is that i have
the problem that looks like this


I have a set of data that i get from analyses


it is a 15 PAGE DATA that is pasted in excel


each page contains 10 SETS OF ENTRIES in 1 column say EID element id so
doing range method would be a hard going.and also i want to standardize
the method so......


the data would look like
COLUMNS:
EID A B C D E F G H I J K
123456
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2
23564
1 2 3 4 5 6 7 8 9 5 6
2 3 6 7 8 9 1 2 6 5 4
5 6 8 9 1 3 4 5 6 5 2


AND SO ON


ok now what i want to do is sort by column K say(Descending) then want
the highest value to be displayed with the Element Id
so that i can move them to a new sheet where i can again sort the same
for say col K again


i was able to do it for 1 Page that is 10 values but i used the Record
Macro button but i dont see it as a good way to go and so wanted to see

how can i do it using do or if command and within it a sort command
that selects the on screen value


thank you in advance


regards
indraneel



Richard Buttrey wrote:
Something like the following will work
The first thing to do is to name the ranges you want sorted, then
build these names into the macro. In the example below, three ranges
"MyRange1", "MyRange2", "MyRange3"

then set the For..Next loop counter accordingly .


Sub SortRanges()
Dim rArray
Dim x As Integer
rArray = Array("MyRange1", "Myrange2", "Myrange3")

For x = 0 To 2
Range(rArray(x)).Sort key1:=Range(rArray(x)).Columns(1),
order1:=xlAscending
Next
End Sub



On 11 Sep 2006 10:09:54 -0700, "indraneel"
wrote:

i want to write a macro
that would contain a do loop
and within that do loop i want to do sorting

can any one help i cant understand of how to select the cells that i
want to sort in VBA language

hope i have made my self clear


regards
indraneel

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________