How can I get the value of a specific column in a range.

[rcrumbliss]

this is probably a really lame question, but I can't seem to figure out how to make this work.

I have a range that I have autofiltered. I then need to look at the rows in that range, to see if a certain variable value falls between the values of columns 6 and 7.

For clarification, I have a data set over 25000 entries in size that contain address ranges. Many of these are single addresses (1234-1234 my lane) but most are ranges (1235-1299 my lane)
I can filter down to the street, but am then left with multiple entries. I just need to get the row for the address I am looking for, and then pull information from 2 columns into a different worksheet.

I dont want to use a for...next loop because I need to reduce the clock cycles this is going to take to process.

This is what I thought would work, but it doesnt.

            Dim rRange As Range, filRange As Range, rng As Range
            
            Set rRange = Sheets("ENTIRE_CENTER").Range("A2:Z30000")
            With rRange
                .AutoFilter
                .AutoFilter Field:=10, Criteria1:=Street
                .AutoFilter Field:=9, Criteria1:=Pfx
                If StrType <> "" And Not IsEmpty(StrType) Then .AutoFilter Field:=11, Criteria1:=StrType
                .AutoFilter Field:=12, Criteria1:=Sfx
                .AutoFilter Field:=15, Criteria1:=Postal
                
                Set filRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
                
                For Each rng In filRange
                    'if StrtNum > min AND StrtNum < Max then = source
                    If StrtNum > rng.Column(6).Value And _
                        StrtNum < rng.Column(7).Value Then
                            If rng.Column(3).Value < 10 Then ctrLoop = "0" & rng.Column(3).Value Else ctrLoop = rng.Column(3).Value
                            OutputTab.Range("N" & y) = "9801" & ctrLoop & rng.Column(4).Value
                    Else
                        'do nothing
                    End If
            
                Next rng
            End With

Can anyone please clue me in on what I need to use instead of rng.Column(#) to access the data?

Thank you in advance

 

[Akuini]

Could you please provide some example data along with the expected results? About 15 rows should be enough.
Please use the XL2BB tool to post your example.

 

[rcrumbliss]

Could you please provide some example data along with the expected results? About 15 rows should be enough.
Please use the XL2BB tool to post your example.

Here is a snippet of the information that I need to filter. I first filter it using columns I through L and O using the values in the second sheet. Then I need to filter out rows where Sheet1 Column F => StreetNumber (column G in 2nd Sheet) and is <= Row G

As you can see, If I filter down to S 175th ST, I am left with 4 rows of data. In this case rows 7,9,10,11. I know my problem lies in how I am referencing those values as (rng.Column(#).Value) I just don't know what the correct way to look at the value in a specific column of only the visible rows in the sorted data to determine which row contains said value.

sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	CenterName	SLIC	Loop	Sequence	Unit	PrimaryLow	PrimaryHigh	Parity	Pfx	StreetName	StreetType	Sfx	City	State	PostalCode	HiLow	Map	Cons Sequence	Consignee
2	KENT	9801	1	5	101	5699	5400		S	178TH	ST		TUKWILA	WA	98188	X
3	KENT	9801	1	10	101	17800	17899			54TH	PL	S	SEATAC	WA	98188		X
4	KENT	9801	1	15	101	5699	4800		S	178TH	ST		SEATAC	WA	98188	X	X
5	KENT	9801	1	20	101	17799	17700			50TH	CT	S	SEATAC	WA	98188	X	X
6	KENT	9801	1	25	101	17598	17500	E		MILITARY	RD	S	SEATAC	WA	98188	X	X
7	KENT	9801	1	30	101	4700	4729		S	175TH	ST		SEATAC	WA	98188		X
8	KENT	9801	1	35	101	17400	17499			47TH	LN	S	SEATAC	WA	98188		X
9	KENT	9801	1	40	101	4730	4799		S	175TH	ST		SEATAC	WA	98188		X
10	KENT	9801	1	40	101	4800	4899		S	175TH	ST		SEATAC	WA	98188		X
11	KENT	9801	1	45	101	5100	5199		S	175TH	ST		SEATAC	WA	98188		X
12	KENT	9801	1	50	101	17398	17300	E		MILITARY	RD	S	SEATAC	WA	98188	X	X
13	KENT	9801	1	55	101	17298	17200	E		MILITARY	RD	S	SEATAC	WA	98188	X	X
14	KENT	9801	1	60	101	4460	4499		S	173RD	ST		SEATAC	WA	98188		X
15	KENT	9801	1	65	101	4700	4899		S	173RD	ST		SEATAC	WA	98188		X
16	KENT	9801	1	70	101	17200	17399			47TH	AVE	S	SEATAC	WA	98188		X
17	KENT	9801	1	75	101	4400	4699		S	172ND	PL		SEATAC	WA	98188		X
18	KENT	9801	1	80	101	4700	4799		S	172ND	PL		SEATAC	WA	98188		X
19	KENT	9801	1	85	101	4800	4899		S	172ND	PL		SEATAC	WA	98188		X
20	KENT	9801	1	90	101	17399	17300			51ST	AVE	S	SEATAC	WA	98188	X	X
All Sequences

sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Region	District	Division	Center	SLIC	Consignee	StreetNum	Pre	Street	Sfx	StreetType	Postal	Suite	Loop	Seq	Pkgs	Stop	ClosedType	DIAD SAC
2	03 - WEST REGION	76 - NORTHWEST	10 - CENTRAL	9801 - TUKWILA-TUKWILA	9801		4755	S	175th		ST	98188		1	45	1	1	KCRouteLY
Data to Sort

 

[Akuini]

This is untested:

            Dim rRange As Range, filRange As Range, rng As Range
            Application.ScreenUpdating = False
            Set rRange = Sheets("ENTIRE_CENTER").Range("A2:Z30000")
            With rRange
                .AutoFilter
                .AutoFilter Field:=10, Criteria1:=Street
                .AutoFilter Field:=9, Criteria1:=Pfx
                If StrType <> "" And Not IsEmpty(StrType) Then .AutoFilter Field:=11, Criteria1:=StrType
                .AutoFilter Field:=12, Criteria1:=Sfx
                .AutoFilter Field:=15, Criteria1:=Postal
                
                'set filRange to first column only (i.e. col A)
                Set filRange = .Columns(1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
                
                For Each rng In filRange
                    'if StrtNum > min AND StrtNum < Max then = source
                    If StrtNum > rng.Offset(, 5).Value And _
                        StrtNum < rng.Offset(, 6).Value Then
                            If rng.Offset(, 2).Value < 10 Then ctrLoop = "0" & rng.Offset(, 2).Value Else ctrLoop = rng.Offset(, 2).Value
                            OutputTab.Range("N" & y) = "9801" & ctrLoop & rng.Offset(, 3).Value
                    Else
                        'do nothing
                    End If
            
                Next rng
            End With
            Application.ScreenUpdating = True

 

[Scott Huish]

Just pointing out a couple things. You are using a For Next loop (For Each...Next)

If's don't require an Else, there is no need for Else 'Do Nothing just leave it off. I didn't check the code other than this.

                  If StrtNum > rng.Offset(, 5).Value And _
                        StrtNum < rng.Offset(, 6).Value Then
                            If rng.Offset(, 2).Value < 10 Then ctrLoop = "0" & rng.Offset(, 2).Value Else ctrLoop = rng.Offset(, 2).Value
                            OutputTab.Range("N" & y) = "9801" & ctrLoop & rng.Offset(, 3).Value
                    End If