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]

That works but only for cells formatted as text. The cells that are already formatted as a number return a #VALUE error.

 

[JoeMo]

Don't see why that should be. If you add zero to a number you get the number. Post some examples of your data showing both numbers and text types.

 

[Justinian]

Ok.

Here is the workbook after I paste data from an external source:

Move-Ins.xlsm
	A	B	C	D	E	F	G
9	34-3405E		06/12/20		6/12	-	4
10	36-3612K		06/12/20		6/13	-	20
11	40-4011F		06/12/20		6/14	-	3
12	44-4407A		06/12/20		6/15	-	9
13	12-1228		06/13/20		6/16	-	4
14	19-1953		06/13/20
15	20-2013		06/13/20
16	33-3305F		06/13/20
17	34-3407A		06/13/20
18	34-3408H		06/13/20
19	34-3410L		06/13/20
20	38-3811C		06/13/20
21	40-4003F		06/13/20
22	42-4200H		06/13/20
23	42-4209F		06/13/20
24	42-4212L		06/13/20
25	43-4308E		06/13/20
26	46-4604G		06/13/20
27	46-4604M		06/13/20
28	47-4705A		06/13/20
29	47-4705C		06/13/20
30	49-4902D		06/13/20
31	50-5001B		06/13/20
32	26A-26A20		06/13/20
33	41-4109C		06/14/20
34	46-4604L		06/14/20
35	48-4807J		06/14/20
36	32-3221		06/15/20
37	34-3410M		06/15/20
38	35-3505L		06/15/20
39	37-3711J		06/15/20
40	37-3712J		06/15/20
41	38-3807C		06/15/20
42	38-3807D		06/15/20
43	48-4801L		06/15/20
44	49-4912C		06/15/20
45	35-3512D		06/16/20
46	38-3805A		06/16/20
47	45-4501B		06/16/20
48	50-5005G		06/16/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,"","-")

Here is the same workbook after I run the macro (you can see the code runs but ignores the cells formatted as text:

Move-Ins.xlsm
	A	B	C	D	E	F	G
9	3405E	3405E	06/12/20		6/12	-	4
10	3612K	3612K	06/12/20		6/13	-	20
11	4011F	4011F	06/12/20		6/14	-	3
12	4407A	4407A	06/12/20		6/15	-	9
13	1228		06/13/20		6/16	-	4
14	1953		06/13/20
15	2013		06/13/20
16	3305F	3305F	06/13/20
17	3407A	3407A	06/13/20
18	3408H	3408H	06/13/20
19	3410L	3410L	06/13/20
20	3811C	3811C	06/13/20
21	4003F	4003F	06/13/20
22	4200H	4200H	06/13/20
23	4209F	4209F	06/13/20
24	4212L	4212L	06/13/20
25	4308E	4308E	06/13/20
26	4604G	4604G	06/13/20
27	4604M	4604M	06/13/20
28	4705A	4705A	06/13/20
29	4705C	4705C	06/13/20
30	4902D	4902D	06/13/20
31	5001B	5001B	06/13/20
32	26A20	505 S. Curson #26A-20	06/13/20
33	4109C	4109C	06/14/20
34	4604L	4604L	06/14/20
35	4807J	4807J	06/14/20
36	3221		06/15/20
37	3410M	3410M	06/15/20
38	3505L	3505L	06/15/20
39	3711J	3711J	06/15/20
40	3712J	3712J	06/15/20
41	3807C	3807C	06/15/20
42	3807D	3807D	06/15/20
43	4801L	4801L	06/15/20
44	4912C	4912C	06/15/20
45	3512D	3512D	06/16/20
46	3805A	3805A	06/16/20
47	4501B	4501B	06/16/20
48	5005G	5005G	06/16/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:B48	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:B48

 

[JoeMo]

Sorry, I don't understand what you mean by: " you can see the code runs but ignores the cells formatted as text: ". And what you show in post #13 doesn't seem consistent with what you said in post #11.

 

[Justinian]

In my second screenshot in post #13, the RIGHT formula ran as well as the VLOOKUP but cells such as B13:B15 are blank because A13:A15 is formatted as text. When I select A13:A15, that yellow diamond-shape with the exclamation point inside it appears and says "the number in this cell is formatted as text." When I click that yellow shape and select "convert to number," B13:B15 now run the VLOOKUP formula. In other words, the macro works in all cells formatted as numbers; those formatted as text are ignored.

 

[JoeMo]

Try removing the quote marks around the numbers in your col B formula like this:

=IFERROR(IF(OR(LEFT(A10,1)=9,LEFT(A10,2)<=32),VLOOKUP(A10,Gardens,2,FALSE),A10),"")

 

[Justinian]

That will not work because then the result is the contents of column A instead of the VLOOKUP. So 1228 in A13 becomes 1228 in B13 instead of the look up value.

 

[JoeMo]

I don't see why this formula in col B won't work:

=IFERROR(IF(OR(LEFT(A9,1)+0=9,LEFT(A9,2)+0<=32),VLOOKUP(A9,Gardens,2,FALSE),A9),"")

regardless of whether the A cell holds a number or text.

 

[Justinian]

When I add those zeros per your formula above, now the VLOOKUP does not work and cells in column A are still formatted as text:

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		06/15/20
14	3408H		06/15/20
15	3410L		06/15/20
16	3410M		06/15/20
17	3505L		06/15/20
18	3612K		06/15/20
19	3711J		06/15/20
20	3712J		06/15/20
21	3805A		06/15/20
22	3807C		06/15/20
23	3807D		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:B23	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:B23

 

[JoeMo]

That's because you didn't use the formula I posted - the formula you show still has quote marks around the 9 and 32 values.