can i condense my code?

[yorkshirewhite]

Hi

I've got some code that works, but its impacting on the size of the spreadsheet severely.

I'm wondering i can do a loop which executes the same code but just increases the row column by 1.

So for example ive got the following code:

If Range("B4").Value = "X" Then
Sheets("Data Sheet").Range("C11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,2,FALSE)-S11-AI11-AY11-BO11-CE11-CU11-DK11-EA11)"
Sheets("Data Sheet").Range("D11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,3,FALSE)-T11-AJ11-AZ11-Bp11-Cf11-Cv11-Dl11-Eb11)"
Sheets("Data Sheet").Range("E11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,4,FALSE)-U11-AK11-BA11-Bq11-Cg11-Cw11-Dm11-Ec11)"
Sheets("Data Sheet").Range("F11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,5,FALSE)-V11-AL11-BB11-Br11-Ch11-Cx11-Dn11-Ed11)"
Sheets("Data Sheet").Range("G11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,6,FALSE)-W11-AM11-BC11-Bs11-Ci11-Cy11-Do11-Ee11)"
Sheets("Data Sheet").Range("H11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,7,FALSE)-X11-AN11-BD11-Bt11-Cj11-Cz11-Dp11-Ef11)"
Sheets("Data Sheet").Range("I11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,8,FALSE)-y11-AO11-bE11-Bu11-Ck11-da11-Dq11-Eg11)"
Sheets("Data Sheet").Range("J11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,9,FALSE)-Z11-AP11-BF11-Bv11-Cl11-db11-Dr11-Eh11)"
Sheets("Data Sheet").Range("K11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,10,FALSE)-AA11-AQ11-bG11-Bw11-Cm11-dc11-Ds11-Ei11)"
Sheets("Data Sheet").Range("L11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,11,FALSE)-Ab11-AR11-bh11-Bx11-Cn11-dd11-Dt11-Ej11)"
Sheets("Data Sheet").Range("M11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,12,FALSE)-Ac11-AS11-bi11-By11-Co11-de11-Du11-Ek11)"
Sheets("Data Sheet").Range("n11") = "=IF($A11=""X"","""",VLOOKUP($B11,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,13,FALSE)-Ad11-AT11-bj11-Bz11-Cp11-df11-Dv11-El11)"
Else
Sheets("Data Sheet").Range("C11") = ""
Sheets("Data Sheet").Range("D11") = ""
Sheets("Data Sheet").Range("E11") = ""
Sheets("Data Sheet").Range("F11") = ""
Sheets("Data Sheet").Range("G11") = ""
Sheets("Data Sheet").Range("H11") = ""
Sheets("Data Sheet").Range("I11") = ""
Sheets("Data Sheet").Range("J11") = ""
Sheets("Data Sheet").Range("K11") = ""
Sheets("Data Sheet").Range("L11") = ""
Sheets("Data Sheet").Range("M11") = ""
Sheets("Data Sheet").Range("n11") = ""
End If

I really need this code to loop increasing by one number on each of the rows, at the moment ive just done the below, but as this happens from rows 11 through to 83...as you can imagine its effecting the spreadsheet size. Any help would be much appreciated

If Range("B5").Value = "X" Then
Sheets("Data Sheet").Range("C12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,2,FALSE)-S12-AI12-AY12-BO12-CE12-CU12-DK12-EA12)"
Sheets("Data Sheet").Range("D12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,3,FALSE)-T12-AJ12-AZ12-Bp12-Cf12-Cv12-Dl12-Eb12)"
Sheets("Data Sheet").Range("E12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,4,FALSE)-U12-AK12-BA12-Bq12-Cg12-Cw12-Dm12-Ec12)"
Sheets("Data Sheet").Range("F12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,5,FALSE)-V12-AL12-BB12-Br12-Ch12-Cx12-Dn12-Ed12)"
Sheets("Data Sheet").Range("G12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,6,FALSE)-W12-AM12-BC12-Bs12-Ci12-Cy12-Do12-Ee12)"
Sheets("Data Sheet").Range("H12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,7,FALSE)-X12-AN12-BD12-Bt12-Cj12-Cz12-Dp12-Ef12)"
Sheets("Data Sheet").Range("I12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,8,FALSE)-y12-AO12-bE12-Bu12-Ck12-da12-Dq12-Eg12)"
Sheets("Data Sheet").Range("J12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,9,FALSE)-Z12-AP12-BF12-Bv12-Cl12-db12-Dr12-Eh12)"
Sheets("Data Sheet").Range("K12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,10,FALSE)-AA12-AQ12-bG12-Bw12-Cm12-dc12-Ds12-Ei12)"
Sheets("Data Sheet").Range("L12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,11,FALSE)-Ab12-AR12-bh12-Bx12-Cn12-dd12-Dt12-Ej12)"
Sheets("Data Sheet").Range("M12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,12,FALSE)-Ac12-AS12-bi12-By12-Co12-de12-Du12-Ek12)"
Sheets("Data Sheet").Range("n12") = "=IF($A12=""X"","""",VLOOKUP($B12,'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,13,FALSE)-Ad12-AT12-bj12-Bz12-Cp12-df12-Dv12-El12)"
Else
Sheets("Data Sheet").Range("C12") = ""
Sheets("Data Sheet").Range("D12") = ""
Sheets("Data Sheet").Range("E12") = ""
Sheets("Data Sheet").Range("F12") = ""
Sheets("Data Sheet").Range("G12") = ""
Sheets("Data Sheet").Range("H12") = ""
Sheets("Data Sheet").Range("I12") = ""
Sheets("Data Sheet").Range("J12") = ""
Sheets("Data Sheet").Range("K12") = ""
Sheets("Data Sheet").Range("L12") = ""
Sheets("Data Sheet").Range("M12") = ""
Sheets("Data Sheet").Range("n12") = ""
End If