Excel too slow due to formula

kmclean · October 29th, 2010, 12:56 PM

Hi,

I have a workbook template that contains contains 5 spreadsheets, each spreadsheet has formulas down to row 10,000 and across 11 columns, it also

first problem My workbook is toooo slow! It has no values and it is set to manual calculation. What can I do to increase the speed.

second problem I want to add macros to help me with the mundane repeative steps of copy from my data sources into my workbook. should I erase the formulas and create macros to paste the formlas to each row that contains data values? or what options do I have.

I am not even sure if I am asking the question right. but I need help please.

mtranchi · October 29th, 2010, 09:46 PM

where are you copying your data from?

second, are those formulas there just in case you have 10,000 rows of data? that's a lot of formulas. most of the excel funtions are supported in VBA, and the ones that aren't have corresponding functions in VBA.

There's several different ways to skin this cat, and I'm gonna guess you have no experience with programming in VBA.

Since i have no idea what you're doing as far as data calculations, here's a sample spreadsheet:

Item Date ordered Qty Price Anticpated Item total
delivery date

Extenze 6/9/69 5 10

The anticipated delivery date and the item total will be calculated fields.

So here's the code:

Code:

Option Explicit

Sub GoDownRows()
Dim Rg As Range 'Declare an object variable that is a range object
Dim lRowOffset As Long 'this is a variable to keep track of which row we're in.

Set Rg = Sheets("Sheet1").Range("A1") 'Change "Sheet1" to whatever the name of your sheet is

With Rg

'____________________________________________________________________________________________________________________________________
    'Use the offset property of the Range object to refer to the cell you want to put a calculated result in. The Offset property is
        'zero-based, so since we've set our range to be cell "A1", then ".Offset(0, 0)" refers to "A1". The first zero is the
        'row offset, the second is the column offset, so ".Offset(1,0)" refers to cell "A2", ".Offset(0,1)" refers to cell "B1", and
        'so on.
    'Because we are within a "With/End With" block, precede the offset property with a period.
    'I'm going to assume you paste your data into cell "A2" of your worksheet, so as we go down the columns, we'll check the "A"
        'column to see if we should enter formulas for this row.
'___________________________________________________________________________________________________________________________________


    lRowOffset = 1 'Set the row number to 2, the one directly under your column headers. Keep in mind that because the offset is
        'zero-based, it's gonna be one less the the actual row we're looking at.
    
    Do Until IsEmpty(.Offset(lRowOffset, 0)) 'if there's data pasted into this row in the "A" column, do it, otherwise exit this loop.
        .Offset(lRowOffset, 4) = .Offset(lRowOffset, 1) + 6 'Add six days to the order date to come up with the delivery date
        
        'multiply the quantity ordered by the price and enter it into column "F"
        .Offset(lRowOffset, 5) = .Offset(lRowOffset, 2) * .Offset(lRowOffset, 3)
        
        lRowOffset = lRowOffset + 1 'Go to the next row
        
    Loop 'go back to the "Do Until" line
End With

Set Rg = Nothing 'Free up your computer's memory
End Sub

Assuming you know nothing of coding, there's a lot i've left out. Don't want to write a book, lol. If you need to use an Excel function, the code would look something like this:

Code:

.Offset(lRowOffset, 6) = WorksheetFunction.VLookup("Extenze", Range("Prices"), 4, False) 'Where "Prices" is a named range that has Item names and prices

Hope that helps, if not, post again.

mtranchi · October 29th, 2010, 10:19 PM

To increase the speed....
Get rid of all those formulas!! Replace them with VBA, or 'macros'. It's gonna get really complicated for you really fast if you don't know VBA, but the fastest most efficient way is to assign the pasted data to an array, compute formulas, then post them back to the spreadsheet. My way, working on each row of data, might give you adequate speed. If not, you're either going to have to take the plunge into learning the VBA programming language, find a pro in VBA who can set it up for you, or find someone who can set up a custom application for you.
You know the particulars of your situation. VBA (macros), is a difficult programming language to work with becuase of it's poor syntax checking. Miss a period, a quotation mark, a parenthesis, it will give you a retarded message that gives you no clue what you did wrong. Having said that, if you have an aptitude for programming, an extreme aptitude for attention to detail, a love of making a ton of work done at the press of a button, no desire to learn or pay for a programming language outside of Microsoft Office, then VBA is for you!!! Lol. VB.Net express is another programming language, it's free, but i'm not sure it interfaces with Microsoft Office, I don't know much of programming outside of VBA myself. I know (or believe I know) VB.net 2008 didn't interface with Office, maybe 2010 does, it shows up in my references anyway.