efficient code

jjst34 · Feb 4th 2004

Hi,, is there a more efficient way to write this/??? Just curious.. If i increase the range excel crashes.. so i had to do a copy/paste about 5-6 times, but it's obviously not efficient. wondering a better way to go about getting the same thing done? Thanks!

Code

Windows("thisbook.xls").Activate
    Range("jw10").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
    
    
    Range("jw10").Select
    Selection.Copy
    Range("jw11,jw12,jw13,jw14,jw15,jw16,jw17,jw18,jw19,jw20,jw21,jw22,jw23,jw24,jw25,jw26,jw27,jw28,jw29,jw30").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    
    Range("jw10").Select
    Selection.Copy
    Range("jw31,jw32,jw33,jw34,jw35,jw36,jw37,jw38,jw39,jw40,jw41,jw42,jw43,jw44,jw45,jw46,jw47,jw48,jw49,jw50").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    
    Range("jw10").Select
    Selection.Copy
    Range("jw51,jw52,jw53,jw54,jw55,jw56,jw57,jw58,jw59,jw60,jw61,jw62,jw63,jw64,jw65,jw66,jw67,jw68,jw69,jw70").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    
    Range("jw10").Select
    Selection.Copy
    Range("jw71,jw72,jw73,jw74,jw75,jw76,jw77,jw78,jw79,jw80,jw81,jw82,jw83,jw84,jw85,jw86,jw87,jw88,jw89,jw90").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    
    Range("jw10").Select
    Selection.Copy
    Range("jw91,jw92,jw93,jw94,jw95,jw96,jw97,jw98,jw99,jw9912,jw9913,jw9914,jw9915,jw9916,jw9917,jw9918,jw9919").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Range("jw10").Select
    Selection.Copy
    Range("jw9920,jw9921,jw9922,jw9923,jw9924,jw9925,jw9926,jw9927,jw9928,jw9929,jw9930,jw9931,jw9932,jw9933").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    
    
    Range("jw10").Select
    Selection.Copy
    Range("jw9934,jw9935,jw9936,jw9937,jw9938,jw9939,jw9940,jw9941,jw9942,jw9943,jw9944,jw9945,jw9946,jw9947,jw9948").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False




Range("total_variance").Select
Call Remove_functions_from_BS


End Sub

Display More

Will Riley · Feb 4th 2004

Quote

Range("jw10").Select

?

Range JW10 is impossible in all my versions of Excel.... my columns stop at IV - umm have I messed something really obvious ?

Wasn't quite sure whether that was a contigous range or not row 10 to 9948 but here's some code for column IV that would work quicker... maybe you will get the idea ?

Sub jsttFormula()

Dim llastRw As Long

llastRw = 9948
With Worksheets("Sheet4").Range("IV10")
.Formula = "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
.AutoFill Destination:=Worksheets("Sheet4") _
.Range("IV10:IV" & llastRw&)

End With
End Sub

Hope this helps

jjst34 · Feb 5th 2004

sorry will, they are named ranges w/in the sheet. Every once in a while I have to add new rows in sorta random places.. so i was sick of changing the cell references.. So i gave them names. this way if i add something i can just add jw9950 etc... anyways.. i assume this changes ur code since they are not all in a row that i need the code in, they skip around... thanks!

Anonymous · Feb 5th 2004

You can assign the formula directly to all of your ranges.

Code

Sub something()
Windows("thisbook.xls").Activate
    Range("jw10,jw11,jw12,jw13,jw14,jw15,jw16,jw17,jw18,jw19,jw20,jw21,jw22,jw23,jw24,jw25,jw26,jw27,jw28,jw29,jw30").FormulaR1C1 = _
        "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
    Range("jw31,jw32,jw33,jw34,jw35,jw36,jw37,jw38,jw39,jw40,jw41,jw42,jw43,jw44,jw45,jw46,jw47,jw48,jw49,jw50").FormulaR1C1 = _
        "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
    Range("jw51,jw52,jw53,jw54,jw55,jw56,jw57,jw58,jw59,jw60,jw61,jw62,jw63,jw64,jw65,jw66,jw67,jw68,jw69,jw70").FormulaR1C1 = _
        "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
    Range("jw71,jw72,jw73,jw74,jw75,jw76,jw77,jw78,jw79,jw80,jw81,jw82,jw83,jw84,jw85,jw86,jw87,jw88,jw89,jw90").FormulaR1C1 = _
        "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
    Range("jw91,jw92,jw93,jw94,jw95,jw96,jw97,jw98,jw99,jw9912,jw9913,jw9914,jw9915,jw9916,jw9917,jw9918,jw9919").FormulaR1C1 = _
        "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
    Range("jw9920,jw9921,jw9922,jw9923,jw9924,jw9925,jw9926,jw9927,jw9928,jw9929,jw9930,jw9931,jw9932,jw9933").FormulaR1C1 = _
        "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
    Range("jw9934,jw9935,jw9936,jw9937,jw9938,jw9939,jw9940,jw9941,jw9942,jw9943,jw9944,jw9945,jw9946,jw9947,jw9948").FormulaR1C1 = _
        "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
Range("total_variance").Select
Call Remove_functions_from_BS
End Sub

Display More

You may also want to see if putting the calculations in manual mode first helps the speed any.

efficient code

Participate now!

Share