VBA Code Alteration

[Justinian]

How do I alter the following code so that instead of autofill stopping at B68, the autofill stops at the last row of data in column A?

Sub VLOOKUP()

' Run VLOOKUP formula starting in B9
Range("B9").Select
Selection.FormulaR1C1 = _
"=IFERROR(IF(OR(LEFT(RC[-1],1)=""9"",LEFT(RC[-1],2)<=""32""),VLOOKUP(RC[-1],Gardens,2,FALSE),RC[-1]),"""")"
Selection.AutoFill Destination:=Range("B9:B68")
Range("B9:B68").Select
Range("A1").Select
End Sub

 

[Justinian]

It still does not work:

Move-Ins.xlsm
	A	B	C	D	E	F	G
8	Building/Unit	Street Address	Scheduled Move-In Date
9	0953		06/15/20		6/15	-	27
10	1823		06/15/20		6/16	-	6
11	1953		06/15/20		6/17	-	6
12	3221		06/15/20		6/18	-	7
13	3305F	3305F	06/15/20
14	3408H	3408H	06/15/20
15	3410L	3410L	06/15/20
16	3410M	3410M	06/15/20
17	3505L	3505L	06/15/20
18	3612K	3612K	06/15/20
19	3711J	3711J	06/15/20
20	3712J	3712J	06/15/20
21	3805A	3805A	06/15/20
22	3807C	3807C	06/15/20
Report

Cell Formulas
Range	Formula
E9:E15	E9	=IFERROR(IF(ROWS($E$8:E8)<2,MIN($C$9:$C$96),AGGREGATE(15,6,$C$9:$C$96/($C$9:$C$96>E8),1)),"")
G9:G15	G9	=IFERROR(IF(E9="","",COUNTIF($C$9:$C$100,E9)),"")
F9:F15	F9	=IF(E9="","","-")
F16:F18	F16	=IF(E16=FALSE,"","-")
B9:B22	B9	=IFERROR(IF(OR(LEFT(A9,1)+0=9,LEFT(A9,2)+0<=32),VLOOKUP(A9,Gardens,2,FALSE),A9),"")

Named Ranges
Name	Refers To	Cells
Gardens	='Garden Addresses'!$A$1:$B$1622	B9:B22

 

[JoeMo]

I think you are getting errors in your formula in col B when there are no alpha characters in col A, but your IFERROR wrapper is covering them up. What happens if you remove the IFERROR wrapper on the formulas in B9:B11?

 

[Justinian]

Removing my IFEERROR wrap simply reveals the error:

Move-Ins.xlsm
	A	B	C	D	E	F	G
8	Building/Unit	Street Address	Scheduled Move-In Date
9	0953	#N/A	06/15/20		6/15	-	27
10	1823	#N/A	06/15/20		6/16	-	6
11	1953	#N/A	06/15/20		6/17	-	6
12	3221	#N/A	06/15/20		6/18	-	7
13	3305F	3305F	06/15/20
14	3408H	3408H	06/15/20
15	3410L	3410L	06/15/20
16	3410M	3410M	06/15/20
17	3505L	3505L	06/15/20
18	3612K	3612K	06/15/20
Report

Cell Formulas
Range	Formula
E9:E15	E9	=IFERROR(IF(ROWS($E$8:E8)<2,MIN($C$9:$C$96),AGGREGATE(15,6,$C$9:$C$96/($C$9:$C$96>E8),1)),"")
G9:G15	G9	=IFERROR(IF(E9="","",COUNTIF($C$9:$C$100,E9)),"")
B9:B18	B9	=IF(OR(LEFT(A9,1)+0=9,LEFT(A9,2)+0<=32),VLOOKUP(A9,Gardens,2,FALSE),A9)
F9:F15	F9	=IF(E9="","","-")
F16:F18	F16	=IF(E16=FALSE,"","-")

Named Ranges
Name	Refers To	Cells
Gardens	='Garden Addresses'!$A$1:$B$1622	B9:B18

 

[JoeMo]

So, that says your VLOOKUP is not able to find the lookup value in the first column of the named range Gardens. But the VLOOKUP is definitely being called, it's just returning #N/A. Can you post a sample of the range Gardens?

 

[Justinian]

The VLOOKUP is able to find the appropriate code but after the formula runs, I have to convert the cells without an alpha character to numbers, then the VLOOKUP works.

Here is a sample of the named range "Gardens:"

Move-Ins.xlsm
	A	B
58	952	394 S. Genesee # 09-52
59	953	392 S. Genesee # 09-53
60	954	390 S. Genesee # 09-54
61	955	388 S. Genesee # 09-55
62	956	386 S. Genesee # 09-56
Garden Addresses

 

[JoeMo]

The Vlookup is working, the issue is you are trying to lookup values that don't exist in your lookup range.

 

[Justinian]

They do exist. If you look at post #23, 953 is the code and it exists in the range from post #25. Remember, once I run the VLOOKUP and convert column A to numbers, the correct address then appears in column B.

 

[JoeMo]

They do exist. If you look at post #23, 953 is the code and it exists in the range from post #25. Remember, once I run the VLOOKUP and convert column A to numbers, the correct address then appears in column B.

Post #23 shows the text value 0953 in col A, not 953. You need to fix your lookup table. The Vlookup is looking for 0953 not 953. If 0953 isn't in the first column of the lookup table Gardens, Vlookup returns #N/A.

 

[Justinian]

I understand why you think that but that is not what is happening. When I change 0953 to 953 to match the Gardens range, the VLOOKUP still returns a blank until i select the cell and convert to number.

Also, look at 1823 in A9. In the Gardens range, 1823 is the code so the VLOOKUP formula should work but it does not, until I convert that to a number as well. Even removing the quotes surrounding 9 and 32 and adding the +0 as you suggested, the formula still does not work until I convert non alpha codes to numbers.

Move-Ins.xlsm
	A	B	C	D	E	F	G
8	Building/Unit	Street Address	Scheduled Move-In Date
9	1823		06/16/20		6/16	-	11
10	953		06/16/20		6/17	-	12
11	2421		06/16/20		6/18	-	7
12	3408H	3408H	06/16/20		6/19	-	7
Report

Cell Formulas
Range	Formula
E9:E12	E9	=IFERROR(IF(ROWS($E$8:E8)<2,MIN($C$9:$C$96),AGGREGATE(15,6,$C$9:$C$96/($C$9:$C$96>E8),1)),"")
F9:F12	F9	=IF(E9="","","-")
G9:G12	G9	=IFERROR(IF(E9="","",COUNTIF($C$9:$C$100,E9)),"")
B9:B12	B9	=IFERROR(IF(OR(LEFT(A9,1)="9",LEFT(A9,2)<="32"),VLOOKUP(A9,Gardens,2,FALSE),A9),"")

Named Ranges
Name	Refers To	Cells
Gardens	='Garden Addresses'!$A$1:$B$1622	B9:B12

 

[Justinian]

Bump.