Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list using two criteria
I need a routine that will identify the rows that have a number in between
two numbers in Column A and also numbers in between two numbers in column B. Column A has numbers between (and including) 1 and 10 Column B has numbers Between (and including) 100 and 200 Columns C - G have coresponding data I1 has the low variable for column A I2 has the high variable for column A J1 has the low variable for column B J2 has the high variable for column B What I need is a routine that after I input the variables of, say 3 and 5 in I1 - I2 and 100 and 125 in J1 and J2 it will return all rows that meet that criteria and list them starting a A1 on Sheet2. Thanks, Ronbo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list using two criteria
How about:
Sub autotransfer() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") Dim k As Long, v1 As Integer, v2 As Integer, v3 As Integer k = 1 v1 = s1.Range("I1") - 1 v2 = s1.Range("I2") + 1 v3 = s1.Range("J1") - 1 v4 = Range("J2") + 1 n = s1.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To n val1 = s1.Cells(i, "A").Value val2 = s1.Cells(i, "B").Value If val1 v1 And val1 < v2 And val2 v3 And val2 < v4 Then s1.Cells(i, "A").EntireRow.Copy s2.Cells(k, "A") k = k + 1 End If Next End Sub So if Sheet1 had values like: 7 129 1 10 98 2 5 95 3 8 120 4 2 119 5 5 113 6 3 115 7 4 114 8 6 119 9 2 117 10 2 118 11 3 123 12 8 113 13 1 106 14 2 109 15 2 90 16 4 112 17 4 111 18 10 104 19 7 129 20 8 121 21 3 99 22 7 90 23 9 126 24 5 97 25 8 128 26 9 107 27 7 101 28 6 121 29 7 120 30 2 91 31 5 127 32 6 110 33 7 129 34 6 99 35 10 115 36 5 103 37 10 103 38 9 119 39 5 114 40 then Sheet2 would get: 5 113 6 3 115 7 4 114 8 3 123 12 4 112 17 4 111 18 5 103 37 5 114 40 -- Gary''s Student - gsnu201001 "Ronbo" wrote: I need a routine that will identify the rows that have a number in between two numbers in Column A and also numbers in between two numbers in column B. Column A has numbers between (and including) 1 and 10 Column B has numbers Between (and including) 100 and 200 Columns C - G have coresponding data I1 has the low variable for column A I2 has the high variable for column A J1 has the low variable for column B J2 has the high variable for column B What I need is a routine that after I input the variables of, say 3 and 5 in I1 - I2 and 100 and 125 in J1 and J2 it will return all rows that meet that criteria and list them starting a A1 on Sheet2. Thanks, Ronbo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list using two criteria
Thanks a lot. That works PERFECT for the example I gave you. However, I can
not get it to work for my actual numbers. The actual data is lat/lon numbers like; -84.118937 33.975996 -84.263263 33.974543 -84.370027 33.96961 return -84.144555 33.968418 -84.26093 33.967335 -84.200077 33.965695 -84.312723 33.96524 -83.597669 33.964855 -83.706095 33.963888 -83.819245 33.962897 -84.505463 33.962111 -84.435417 33.960959 return -84.532709 33.959403 -84.141554 33.958487 So using I1/I2 as -84.44 and -84.3625 and J1/J2 as 33.955 and 34.02 it returns all. That is due to +1 and -1. Changing those to 0, returns nothing. It should retrun the two as noted, but I can't get it to work. What needs to be changed? Again, thanks for help. Ronbo "Gary''s Student" wrote: How about: Sub autotransfer() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") Dim k As Long, v1 As Integer, v2 As Integer, v3 As Integer k = 1 v1 = s1.Range("I1") - 1 v2 = s1.Range("I2") + 1 v3 = s1.Range("J1") - 1 v4 = Range("J2") + 1 n = s1.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To n val1 = s1.Cells(i, "A").Value val2 = s1.Cells(i, "B").Value If val1 v1 And val1 < v2 And val2 v3 And val2 < v4 Then s1.Cells(i, "A").EntireRow.Copy s2.Cells(k, "A") k = k + 1 End If Next End Sub So if Sheet1 had values like: 7 129 1 10 98 2 5 95 3 8 120 4 2 119 5 5 113 6 3 115 7 4 114 8 6 119 9 2 117 10 2 118 11 3 123 12 8 113 13 1 106 14 2 109 15 2 90 16 4 112 17 4 111 18 10 104 19 7 129 20 8 121 21 3 99 22 7 90 23 9 126 24 5 97 25 8 128 26 9 107 27 7 101 28 6 121 29 7 120 30 2 91 31 5 127 32 6 110 33 7 129 34 6 99 35 10 115 36 5 103 37 10 103 38 9 119 39 5 114 40 then Sheet2 would get: 5 113 6 3 115 7 4 114 8 3 123 12 4 112 17 4 111 18 5 103 37 5 114 40 -- Gary''s Student - gsnu201001 "Ronbo" wrote: I need a routine that will identify the rows that have a number in between two numbers in Column A and also numbers in between two numbers in column B. Column A has numbers between (and including) 1 and 10 Column B has numbers Between (and including) 100 and 200 Columns C - G have coresponding data I1 has the low variable for column A I2 has the high variable for column A J1 has the low variable for column B J2 has the high variable for column B What I need is a routine that after I input the variables of, say 3 and 5 in I1 - I2 and 100 and 125 in J1 and J2 it will return all rows that meet that criteria and list them starting a A1 on Sheet2. Thanks, Ronbo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list using two criteria
This works for non-integers:
Sub autotransfer() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") Dim k As Long, v1 As Double, v2 As Double, v3 As Double k = 1 v1 = s1.Range("I1") v2 = s1.Range("I2") v3 = s1.Range("J1") v4 = Range("J2") n = s1.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To n val1 = s1.Cells(i, "A").Value val2 = s1.Cells(i, "B").Value If val1 v1 And val1 < v2 And val2 v3 And val2 < v4 Then s1.Cells(i, "A").EntireRow.Copy s2.Cells(k, "A") k = k + 1 End If If i = 3 Then End If Next End Sub -- Gary''s Student - gsnu201001 "Ronbo" wrote: Thanks a lot. That works PERFECT for the example I gave you. However, I can not get it to work for my actual numbers. The actual data is lat/lon numbers like; -84.118937 33.975996 -84.263263 33.974543 -84.370027 33.96961 return -84.144555 33.968418 -84.26093 33.967335 -84.200077 33.965695 -84.312723 33.96524 -83.597669 33.964855 -83.706095 33.963888 -83.819245 33.962897 -84.505463 33.962111 -84.435417 33.960959 return -84.532709 33.959403 -84.141554 33.958487 So using I1/I2 as -84.44 and -84.3625 and J1/J2 as 33.955 and 34.02 it returns all. That is due to +1 and -1. Changing those to 0, returns nothing. It should retrun the two as noted, but I can't get it to work. What needs to be changed? Again, thanks for help. Ronbo "Gary''s Student" wrote: How about: Sub autotransfer() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") Dim k As Long, v1 As Integer, v2 As Integer, v3 As Integer k = 1 v1 = s1.Range("I1") - 1 v2 = s1.Range("I2") + 1 v3 = s1.Range("J1") - 1 v4 = Range("J2") + 1 n = s1.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To n val1 = s1.Cells(i, "A").Value val2 = s1.Cells(i, "B").Value If val1 v1 And val1 < v2 And val2 v3 And val2 < v4 Then s1.Cells(i, "A").EntireRow.Copy s2.Cells(k, "A") k = k + 1 End If Next End Sub So if Sheet1 had values like: 7 129 1 10 98 2 5 95 3 8 120 4 2 119 5 5 113 6 3 115 7 4 114 8 6 119 9 2 117 10 2 118 11 3 123 12 8 113 13 1 106 14 2 109 15 2 90 16 4 112 17 4 111 18 10 104 19 7 129 20 8 121 21 3 99 22 7 90 23 9 126 24 5 97 25 8 128 26 9 107 27 7 101 28 6 121 29 7 120 30 2 91 31 5 127 32 6 110 33 7 129 34 6 99 35 10 115 36 5 103 37 10 103 38 9 119 39 5 114 40 then Sheet2 would get: 5 113 6 3 115 7 4 114 8 3 123 12 4 112 17 4 111 18 5 103 37 5 114 40 -- Gary''s Student - gsnu201001 "Ronbo" wrote: I need a routine that will identify the rows that have a number in between two numbers in Column A and also numbers in between two numbers in column B. Column A has numbers between (and including) 1 and 10 Column B has numbers Between (and including) 100 and 200 Columns C - G have coresponding data I1 has the low variable for column A I2 has the high variable for column A J1 has the low variable for column B J2 has the high variable for column B What I need is a routine that after I input the variables of, say 3 and 5 in I1 - I2 and 100 and 125 in J1 and J2 it will return all rows that meet that criteria and list them starting a A1 on Sheet2. Thanks, Ronbo |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list using two criteria
PERTECT... Your time and expertise is appreciated.
Thanks again, Ronbo "Gary''s Student" wrote: This works for non-integers: Sub autotransfer() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") Dim k As Long, v1 As Double, v2 As Double, v3 As Double k = 1 v1 = s1.Range("I1") v2 = s1.Range("I2") v3 = s1.Range("J1") v4 = Range("J2") n = s1.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To n val1 = s1.Cells(i, "A").Value val2 = s1.Cells(i, "B").Value If val1 v1 And val1 < v2 And val2 v3 And val2 < v4 Then s1.Cells(i, "A").EntireRow.Copy s2.Cells(k, "A") k = k + 1 End If If i = 3 Then End If Next End Sub -- Gary''s Student - gsnu201001 "Ronbo" wrote: Thanks a lot. That works PERFECT for the example I gave you. However, I can not get it to work for my actual numbers. The actual data is lat/lon numbers like; -84.118937 33.975996 -84.263263 33.974543 -84.370027 33.96961 return -84.144555 33.968418 -84.26093 33.967335 -84.200077 33.965695 -84.312723 33.96524 -83.597669 33.964855 -83.706095 33.963888 -83.819245 33.962897 -84.505463 33.962111 -84.435417 33.960959 return -84.532709 33.959403 -84.141554 33.958487 So using I1/I2 as -84.44 and -84.3625 and J1/J2 as 33.955 and 34.02 it returns all. That is due to +1 and -1. Changing those to 0, returns nothing. It should retrun the two as noted, but I can't get it to work. What needs to be changed? Again, thanks for help. Ronbo "Gary''s Student" wrote: How about: Sub autotransfer() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") Dim k As Long, v1 As Integer, v2 As Integer, v3 As Integer k = 1 v1 = s1.Range("I1") - 1 v2 = s1.Range("I2") + 1 v3 = s1.Range("J1") - 1 v4 = Range("J2") + 1 n = s1.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To n val1 = s1.Cells(i, "A").Value val2 = s1.Cells(i, "B").Value If val1 v1 And val1 < v2 And val2 v3 And val2 < v4 Then s1.Cells(i, "A").EntireRow.Copy s2.Cells(k, "A") k = k + 1 End If Next End Sub So if Sheet1 had values like: 7 129 1 10 98 2 5 95 3 8 120 4 2 119 5 5 113 6 3 115 7 4 114 8 6 119 9 2 117 10 2 118 11 3 123 12 8 113 13 1 106 14 2 109 15 2 90 16 4 112 17 4 111 18 10 104 19 7 129 20 8 121 21 3 99 22 7 90 23 9 126 24 5 97 25 8 128 26 9 107 27 7 101 28 6 121 29 7 120 30 2 91 31 5 127 32 6 110 33 7 129 34 6 99 35 10 115 36 5 103 37 10 103 38 9 119 39 5 114 40 then Sheet2 would get: 5 113 6 3 115 7 4 114 8 3 123 12 4 112 17 4 111 18 5 103 37 5 114 40 -- Gary''s Student - gsnu201001 "Ronbo" wrote: I need a routine that will identify the rows that have a number in between two numbers in Column A and also numbers in between two numbers in column B. Column A has numbers between (and including) 1 and 10 Column B has numbers Between (and including) 100 and 200 Columns C - G have coresponding data I1 has the low variable for column A I2 has the high variable for column A J1 has the low variable for column B J2 has the high variable for column B What I need is a routine that after I input the variables of, say 3 and 5 in I1 - I2 and 100 and 125 in J1 and J2 it will return all rows that meet that criteria and list them starting a A1 on Sheet2. Thanks, Ronbo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create list based on criteria | Excel Worksheet Functions | |||
Create list from criteria | Excel Discussion (Misc queries) | |||
Can you create a LIST and DATA VALIDATION CRITERIA in same cell? | Excel Discussion (Misc queries) | |||
Create list of text matching criteria | Excel Worksheet Functions | |||
Create a list based on single shared criteria | Excel Worksheet Functions |