sum worksheet function error

[djblois1]

I am trying to switch these:

Please Login or Register  to view this content.

over to this:

Please Login or Register  to view this content.

This first code works perfectly. The new code gives me a compile error. What am I doing wrong? I thought I copied it correctly from online.

[arlu1201]

If the first one is already working properly, why do you want to change it to the 2nd one? Is it not giving you the correct answer?

[djblois1]

It is giving me the correct answer but I have an add-in that does a lot to a worksheet and the worksheet can be any size. It is fine for small worksheets but large worksheets take so long it locks the system - so now I need to find ways to speed up my program and use fewer system resources. I have read that doing it this way is supposed to run quicker (and I do a lot of formulas). So this is one of the ways I am trying to speed it up - I need significant speed increases.

[romperstomper]

Assuming that PvtData is a table on the same sheet, it would be something like:

Please Login or Register  to view this content.

[djblois1]

That worked perfect. Now is there a way to do that with this:

Please Login or Register  to view this content.

I tried this:

Please Login or Register  to view this content.

[romperstomper]

Please Login or Register  to view this content.

[djblois1]

ok that is how I get it to work with Absolute References. How would I do the same thing to work with relative references like this:\

Please Login or Register  to view this content.

Than you for all your help!!

[romperstomper]

Simplest way:

Please Login or Register  to view this content.

[djblois1]

I am trying to get rid of the double step, in this case - is it not possible?

[romperstomper]

It is possible but takes more work and is almost certainly slower. I don't know why you think using worksheet functions in code is faster than using them on a worksheet anyway?

[djblois1]

I read online that it runs quicker plus logically (to me at least) I think it should be quicker because currently I do the formula then I paste the value over it - this way it is done in one step.

[romperstomper]

Where did you read that? Have you actually tested it? Using worksheet functions in code is generally not faster than using formulas in my experience. In any event the difference will be marginal so I suspect you are making micro-optimisations when you really need macro-optimisation. (i.e. to rethink the whole layout)

[djblois1]

One place I read it is here:

http://www.excelforum.com/excel-prog...ml#post2625144

I need to speed it up drastically for large files. I use a LOT of formulas so even marginal increases will add up. I am restructuring the logic also but there is not much of a change that I can do in that - as that is pretty optimized at this point (I have been updating my add-in for 5 years now and have continuously improved the logic)

[romperstomper]

I don't see anything in that post that shows that code is faster than formulas. There is certainly nothing to indicate a speed comparison between entering a formula and replacing it with its value and performing the same calculation in code.
Have you tested it?