Create a list using two criteria

[Ronbo]

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

 

[Gary''s Student]

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

 

[Ronbo]

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]

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

 

[Ronbo]

PERTECT... Your time and expertise is appreciated.

Thanks again,
Ronbo