SUMIF into VBA Userform

[adamheon]

I just added a formula to a table

Please Login or Register  to view this content.

and for some reason my userform that referenced that particular column now is very slow and is taking considerable amount of time to load and refresh.... not sure why this particular formula did affects it this way but when I remove it everything speeds up and when i add it everything slows down.

is there a way to accomplish this formula in a vba code instead (i assume that might help speed things up).

basically the textbox in the user form is called "billedtodate"....i would like to search table4 and sum all the values that match the job number entered into text box "JobSearch".

table 4 contains the job number in column 1 and the $ amount in column 3.

[bakerman2]

Just for testing purposes.

Please Login or Register  to view this content.

Make sure table4 sheet is the activesheet, replace red 1 by some job number and run code.

[BadlySpelledBuoy]

If there is nothing above/below the table on the sheet I would be tempted to use a nice simple approach such as:

Please Login or Register  to view this content.

Sheet names and ranges would need altering to suit obviously.

BSB

[adamheon]

Thanks BSB...worked perfect and everything is as fast as it was before! Thank you!

[bakerman2]

Why calculate entire column ?

Please Login or Register  to view this content.

[BadlySpelledBuoy]

Good point, well raised! However, what would be the advantage of running 4 lines of code to calculate on a smaller range over 1 line of code to calculate the entire column? Speed?

Keep in mind that there is an upper limit on the amount of code you can have in a UserForm before you receive the "Procedure too large" message and need to start moving code into modules. We have no idea how complex the UserForm in question here is. 4 lines of code could be enough to tip it over the edge.

Having said that, your 4 lines could of course be condensed into 1 line, but it would still be longer than the 1 line I suggested and still provide no real benefit IMO.

BSB

[bakerman2]

Keep in mind that there is an upper limit on the amount of code you can have in a UserForm before you receive the "Procedure too large"

There's a 64kb upper limit in a module, before you reach that limit the human mind has given up a long time before in trying to understand what that code with such length is all about.

And yes IMO speed is of great importance. This is a simple formula but with more complex formulas every line that hasn't to be calculated is one line won.

[BadlySpelledBuoy]

Haha! I agree, but I've seen many multipage userforms with many many controls and the code DOES run beyond that.

64k really isn't that much code.

All I'm saying is, this could be one of those userforms that suffers from it.


BSB