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

[shg]

Maybe like this:

    With Sheets("Data Sheet").Range("C11:N11")
        If Range("B4").Value = "X" Then
            .Formula = "=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)"
        Else
            .ClearContents
        End If
    End With

However, I'd look at making other changes to make the formula less complicated.

[Richard Buttrey]

Hi,

You could set a simple loop variable to represent the row number, and use the variable in the loop - untested but something like

Sub Test
Dim x as Integer
  For x = 11 to 83

  Sheets("Data Sheet").Range("C1").Cells(x,1) = "=IF($A" & x &  ""X"","""",VLOOKUP($B" & x & ",'S:\Managers\STOCK MANAGEMENT\[1wakefield keytrol file.xls]1wakefield keytrol file'!$A:$O,2,FALSE)-S" & x & "-AI" & x & "-AY" & x & "-BO" & x & "-CE" & x & "-CU" & x & "-DK" & x & "-EA" & x & ")"
  Next x
End Sub

However I'd be inclined to hold that Vlookup formula in a master cell, say C1, and give it a name. Then have the macro just copy it and paste it to C11:C83 in one hit. So suppose the C1 cell is named "MyForm" use a single line of macro code something like:

Range("MyForm").Copy Destination:=Range("C11:C83")

The problem with your existing code is that you are jumping backwards and forwards between the Visual Basic Environment (VBE) and Excel itself. There is a large time overhead associated with this. Far Better to just do a single Copy in one hit. You should find this speeds up your code and is far tidier to administer.

HTH