excel vba to drag down VLOOKUP formulas

[BORUCH]

hi

i have VLOOKUP formulas all over my worksheet starting from row 12 different spots i would like a vba that would find them all and drag it down same behavior like the fil handle till the first empty

thanks

 

[MARK858]

The first empty where exactly?

 

[BORUCH]

ok lets say i have a VLOOKUP formula in cell j12 and cell k12 in the cell to the left of j12 there is data there in addition there is an empty row after the set of data i want it dragged down till the first empty row

 

[MARK858]

One way, the macro below

Sub Fdown()
    Dim lr As Long
    lr = Worksheets("Sheet3").Cells(Rows.Count, "I").End(xlUp).Row
    Worksheets("Sheet3").Range("J12:K" & lr).FillDown
End Sub

turns

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	horse	19	36	46	31	14
4	pig	21	50	49	25	36
5	lion	44	18	32	31	35
6	tiger	25	35	46	11	47
7
8
9
10
11
12			=VLOOKUP(N12,$A$1:$F$6,3,0)	=VLOOKUP(O12,$A$1:$F$6,5,0)					a	18	28			lion	dog
13			#N/A	#N/A					a					cat	pig
14			#N/A	#N/A					a					dog	horse
15			#N/A	#N/A					a					pig	lion
16			#N/A	#N/A					a					horse	cat
Sheet3

Cell Formulas
Range	Formula
C12:D16	C12	=FORMULATEXT(J12)
J12	J12	=VLOOKUP(N12,$A$1:$F$6,3,0)
K12	K12	=VLOOKUP(O12,$A$1:$F$6,5,0)

into

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	dog	17	35	14	28	50
2	cat	31	26	44	43	30
3	horse	19	36	46	31	14
4	pig	21	50	49	25	36
5	lion	44	18	32	31	35
6	tiger	25	35	46	11	47
7
8
9
10
11
12			=VLOOKUP(N12,$A$1:$F$6,3,0)	=VLOOKUP(O12,$A$1:$F$6,5,0)					a	18	28			lion	dog
13			=VLOOKUP(N13,$A$1:$F$6,3,0)	=VLOOKUP(O13,$A$1:$F$6,5,0)					a	26	25			cat	pig
14			=VLOOKUP(N14,$A$1:$F$6,3,0)	=VLOOKUP(O14,$A$1:$F$6,5,0)					a	35	31			dog	horse
15			=VLOOKUP(N15,$A$1:$F$6,3,0)	=VLOOKUP(O15,$A$1:$F$6,5,0)					a	50	31			pig	lion
16			=VLOOKUP(N16,$A$1:$F$6,3,0)	=VLOOKUP(O16,$A$1:$F$6,5,0)					a	36	43			horse	cat
Sheet3

Cell Formulas
Range	Formula
C12:D16	C12	=FORMULATEXT(J12)
J12:J16	J12	=VLOOKUP(N12,$A$1:$F$6,3,0)
K12:K16	K12	=VLOOKUP(O12,$A$1:$F$6,5,0)

 

[BORUCH]

One way, the macro below

Sub Fdown()
    Dim lr As Long
    lr = Worksheets("Sheet3").Cells(Rows.Count, "I").End(xlUp).Row
    Worksheets("Sheet3").Range("J12:K" & lr).FillDown
End Sub

turns

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	horse	19	36	46	31	14
4	pig	21	50	49	25	36
5	lion	44	18	32	31	35
6	tiger	25	35	46	11	47
7
8
9
10
11
12			=VLOOKUP(N12,$A$1:$F$6,3,0)	=VLOOKUP(O12,$A$1:$F$6,5,0)					a	18	28			lion	dog
13			#N/A	#N/A					a					cat	pig
14			#N/A	#N/A					a					dog	horse
15			#N/A	#N/A					a					pig	lion
16			#N/A	#N/A					a					horse	cat
Sheet3

Cell Formulas
Range	Formula
C12:D16	C12	=FORMULATEXT(J12)
J12	J12	=VLOOKUP(N12,$A$1:$F$6,3,0)
K12	K12	=VLOOKUP(O12,$A$1:$F$6,5,0)

into

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	dog	17	35	14	28	50
2	cat	31	26	44	43	30
3	horse	19	36	46	31	14
4	pig	21	50	49	25	36
5	lion	44	18	32	31	35
6	tiger	25	35	46	11	47
7
8
9
10
11
12			=VLOOKUP(N12,$A$1:$F$6,3,0)	=VLOOKUP(O12,$A$1:$F$6,5,0)					a	18	28			lion	dog
13			=VLOOKUP(N13,$A$1:$F$6,3,0)	=VLOOKUP(O13,$A$1:$F$6,5,0)					a	26	25			cat	pig
14			=VLOOKUP(N14,$A$1:$F$6,3,0)	=VLOOKUP(O14,$A$1:$F$6,5,0)					a	35	31			dog	horse
15			=VLOOKUP(N15,$A$1:$F$6,3,0)	=VLOOKUP(O15,$A$1:$F$6,5,0)					a	50	31			pig	lion
16			=VLOOKUP(N16,$A$1:$F$6,3,0)	=VLOOKUP(O16,$A$1:$F$6,5,0)					a	36	43			horse	cat
Sheet3

Cell Formulas
Range	Formula
C12:D16	C12	=FORMULATEXT(J12)
J12:J16	J12	=VLOOKUP(N12,$A$1:$F$6,3,0)
K12:K16	K12	=VLOOKUP(O12,$A$1:$F$6,5,0)

ok but its not always in the same position i need the formula to find all VLOOKUP's wherever they are on the active sheet and fill it down

 

[BORUCH]

ok but its not always in the same position i need the formula to find all VLOOKUP's wherever they are on the active sheet and fill it down

There is however a blank line between each group so wherever the vlookups are it will hit a blank row

 

[MARK858]

With the further information you have provided I think you need to provide an XL2BB of your data

It is not practical for me to code and search for random Vlookup's on a sheet.
Are there other formulas other than Vlookup's on the sheet?
Are the Vlookup's in a specific area?

I am at work now for the next 12 hours plus and will see if I get a chance to look at it when I get home

 

[BORUCH]

With the further information you have provided I think you need to provide an XL2BB of your data

It is not practical for me to code and search for random Vlookup's on a sheet.
Are there other formulas other than Vlookup's on the sheet?
Are the Vlookup's in a specific area?

I am at work now for the next 12 hours plus and will see if I get a chance to look at it when I get home

see below the yellow is where the VLOOKUP's are

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
12	Item #	Description	Other 1	Other 2	Other 3	Qty	Price	Qty	Price	Qty	Price	Qty	Price
13	00000	test 1				10	12	20	11	VLOOKUP	VLOOKUP	30	10
14	00001	test 2								drag down	drag down
15	00002	test 3
16
17	00004	test 5				9	13	VLOOKUP	VLOOKUP	25	9
18	00005	test 6						drag down	drag down
19	00006	test 7
20
21	00008	test 9				10	14	20	15.66	VLOOKUP	VLOOKUP	30	14
22
23	00011	test 10				5	100	VLOOKUP	VLOOKUP	10	90
24	00012	test 11						drag down	drag down
25	00013	test 12
26	00014	test 13
27	00015	test 14
28	00016	test 15
Sheet1

 

[MARK858]

You're not referencing those merged cells in your Vlookup's are you?

 

[BORUCH]

You're not referencing those merged cells in your Vlookup's are you?

No im not