Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select range higher than and lower than
Hi
I need to write a macro that selects numers in a coloumn that are higher than e.g. 5999999 but lower than 6999999 and copy them. Any ideas? The numbers are sorted according to value, so the lowest number is first and the highest number is last in the coloumn. Any help will be appreciated. //Kasper |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select range higher than and lower than
Here is a very simple sub (there are more sophisticated ways)
Sub findthem() Set myrange = Range("A1:A30") mymin = 5999999 mymax = 6999999 j = 1 For Each myvalue In myrange If myvalue mymin And myvalue < mymax Then Cells(j, 7) = myvalue j = j + 1 End If Next End Sub I have assumed the numbers are in A1:A30; change the first statement as needed. I have placed the results in column G starting with G1; change j=1 to some other value to start lower down and change the 7 in Cells(j,7) to use another column. Depending on your needs, you may want If myvalue = mymin And myvalue =< mymax Then best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kasper" wrote in message ... Hi I need to write a macro that selects numers in a coloumn that are higher than e.g. 5999999 but lower than 6999999 and copy them. Any ideas? The numbers are sorted according to value, so the lowest number is first and the highest number is last in the coloumn. Any help will be appreciated. //Kasper |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select range higher than and lower than
Macro not needed - the Advanced filter functionality can handle it.
Say your list has the heading Numbers. In some unused area of the spreadsheet, enter these values in 2 rows and 2 columns Numbers Numbers 599999 <699999 Select the Numbers list and click Data, Filter, Advanced Filter Select Copy to another location, the List range should be populated with the address of the Numbers list, for Criteria Range enter the range where you entered the above 4 values, for Copy To enter the address of a cell where you want the filtered list to begin. -- TedMi "Kasper" wrote: Hi I need to write a macro that selects numers in a coloumn that are higher than e.g. 5999999 but lower than 6999999 and copy them. Any ideas? The numbers are sorted according to value, so the lowest number is first and the highest number is last in the coloumn. Any help will be appreciated. //Kasper |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select range higher than and lower than
On 22 Okt., 16:19, "Bernard Liengme"
wrote: Here is a very simple sub (there are more sophisticated ways) Sub findthem() * *Set myrange = Range("A1:A30") * *mymin = 5999999 * *mymax = 6999999 * *j = 1 * *For Each myvalue In myrange * * *If myvalue mymin And myvalue < mymax Then * * * *Cells(j, 7) = myvalue * * * *j = j + 1 * * *End If * *Next End Sub I have assumed the numbers are in A1:A30; change the first statement as needed. I have placed the results in column G starting with G1; change j=1 to some other value to start lower down and change the 7 in Cells(j,7) to use another column. *Depending on your needs, you may want If myvalue = mymin And myvalue =< mymax Then best wishes -- Bernard V Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme remove caps from email "Kasper" wrote in message ... Hi I need to write a macro that selects numers in a coloumn that are higherthane.g. 5999999 but lowerthan6999999 and copy them. Any ideas? The numbers are sorted according to value, so the lowest number is first and the highest number is last in the coloumn. Any help will be appreciated. //Kasper- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - Thanks for the answer... I need the macro to select the complete row for each of the values in the coloumn that is in the selected range and then make it copy it to another sheet(that part is not too difficult though) //Kasper |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select range higher than and lower than
On 22 Okt., 16:38, tedmi wrote:
Macro not needed - the Advanced filter functionality can handle it. Say your list has the heading Numbers. In some unused area of the spreadsheet, enter these values in 2 rows and 2 columns Numbers * *Numbers 599999 * *<699999 Select the Numbers list and click Data, Filter, Advanced Filter Select Copy to another location, the List range should be populated with the address of the Numbers list, for Criteria Range enter the range where you entered the above 4 values, for Copy To enter the address of a cell where you want the filtered list to begin. -- TedMi "Kasper" wrote: Hi I need to write a macro that selects numers in a coloumn that are higherthane.g. 5999999 but lowerthan6999999 and copy them. Any ideas? The numbers are sorted according to value, so the lowest number is first and the highest number is last in the coloumn. Any help will be appreciated. //Kasper- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - Sorry but I do need a Macro, since I need to automate the process //Kasper |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select range higher than and lower than
On 23 Okt., 12:50, Kasper wrote:
On 22 Okt., 16:19, "Bernard Liengme" wrote: Here is a very simple sub (there are more sophisticated ways) Sub findthem() * *Set myrange = Range("A1:A30") * *mymin = 5999999 * *mymax = 6999999 * *j = 1 * *For Each myvalue In myrange * * *If myvalue mymin And myvalue < mymax Then * * * *Cells(j, 7) = myvalue * * * *j = j + 1 * * *End If * *Next End Sub I have assumed the numbers are in A1:A30; change the first statement as needed. I have placed the results in column G starting with G1; change j=1 to some other value to start lower down and change the 7 in Cells(j,7) to use another column. *Depending on your needs, you may want If myvalue = mymin And myvalue =< mymax Then best wishes -- Bernard V Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme remove caps from email "Kasper" wrote in message .... Hi I need to write a macro that selects numers in a coloumn that are higherthane.g. 5999999 but lowerthan6999999 and copy them. Any ideas? The numbers are sorted according to value, so the lowest number is first and the highest number is last in the coloumn. Any help will be appreciated. //Kasper- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - Thanks for the answer... I need the macro to select the complete row for each of the values in the coloumn that is in the selected range and then make it copy it to another sheet(that part is not too difficult though) //Kasper- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - But as I mentioned before, I need to get the macro to select e.g. Row 820 to Row 840(since these rows contain contain "myvalue" in coloumn D) and then copy Everything in Row 820 to Row 840. Any further help will be appreciated. //Kasper |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select range higher than and lower than
On 23 Okt., 13:11, Kasper wrote:
On 23 Okt., 12:50, Kasper wrote: On 22 Okt., 16:19, "Bernard Liengme" wrote: Here is a very simple sub (there are more sophisticated ways) Sub findthem() * *Set myrange = Range("A1:A30") * *mymin = 5999999 * *mymax = 6999999 * *j = 1 * *For Each myvalue In myrange * * *If myvalue mymin And myvalue < mymax Then * * * *Cells(j, 7) = myvalue * * * *j = j + 1 * * *End If * *Next End Sub I have assumed the numbers are in A1:A30; change the first statement as needed. I have placed the results in column G starting with G1; change j=1 to some other value to start lower down and change the 7 in Cells(j,7) to use another column. *Depending on your needs, you may want If myvalue = mymin And myvalue =< mymax Then best wishes -- Bernard V Liengme Microsoft Excel MVPhttp://people.stfx.ca/bliengme remove caps from email "Kasper" wrote in message .... Hi I need to write a macro that selects numers in a coloumn that are higherthane.g. 5999999 but lowerthan6999999 and copy them. Any ideas? The numbers are sorted according to value, so the lowest number is first and the highest number is last in the coloumn. Any help will be appreciated. //Kasper- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - Thanks for the answer... I need the macro to select the complete row for each of the values in the coloumn that is in the selected range and then make it copy it to another sheet(that part is not too difficult though) //Kasper- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - But as I mentioned before, I need to get the macro to select e.g. Row 820 to Row 840(since these rows contain contain "myvalue" in coloumn D) and then copy Everything in Row 820 to Row 840. Any further help will be appreciated. //Kasper- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - Okay, figured it out with this Macro. But the Activsheets.PAste command doesn't work with it: Sub findceller_dataark() Range("A1:F2067").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("D2").Select Range(Selection, Selection.End(xlDown)).Select Selection.TextToColumns Destination:=Range("D2"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 9), Array(4, 1)), TrailingMinusNumbers:=True Range("D1").Select Selection.AutoFilter Field:=4, Criteria1:="<800000", Operator:=xlAnd, _ Criteria2:="699999" Selection.End(xlToLeft).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("Ark2").Select Range("A1").Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Can I flag numbers that are higher/lower than a range of #'s | Excel Worksheet Functions | |||
need to select closest match using vlookup if it higher or lower | Excel Discussion (Misc queries) | |||
How can I find the row nr of the first number lower/ higher then a | Excel Discussion (Misc queries) |