Speed Up This Macro

**sbrnard** · 10-07-2016, 10:14 AM

The attached spreadsheet is used to assign a job number to a contract. There are 2 macros - FindIt and OpenJobNumberForm. FindIt scans the list to see if the contract # being entered has already been entered. OpenJobNumberForm opens a form to allow the user to enter the date and the contract number. A formula in the spreadsheet assigns the next number in the sequence as the job number.

The issue is that the OpenJobNumberForm macro scans the entire spreadsheet before assigning a new job number. As the list gets longer, this process takes longer. Is there a way to speed this up (ie eliminate the need to scan the entire spreadsheet)?

Thanks in advance for any help.

**dflak** · 10-07-2016, 12:06 PM

About all I can offer is instead of

Please Login or Register  to view this content.

At the beginning of the module use the following code:

Please Login or Register  to view this content.

Then you can replace all the select sheet / activesheet with sh.whatever

For example, instead of

Please Login or Register  to view this content.

You can use

Please Login or Register  to view this content.

Likewise, look for opportunities to remove references to the activecell. sh.Cells(RowNumber, ColumnNumber) might help.

So instead of constantly moving the active cell down or over, use an index

Please Login or Register  to view this content.

In general when you see
Something.Select followed by Selection.DoSomething, you can replace it with Something.DoSomething.

Don't depend on activesheet or activecell. Your concept of what's active and Excel's concept of what's active may be two different things. Although you seem to take pains to make sure to activate what you want.

I don't really know how much time it takes excel to activate or select something, but I think it works faster with direct references, and it may be easier to figure out where you are as well instead of doing all those offsets in your head.