I have 14,900 Lines of code in my Sub, Could someone show me how to be more efficient?

**BoBoCoDeR** · 05-07-2012, 12:02 AM

I am sure the entire Sub does exactly what I need it to do except for the fact the compiler said No Way! It is too large to compile.
I have 14,900 lines of code.

I am not asking for some fish.
I am looking for some one to teach me how to fish here.

I am sure there is a way I could use a clever loop here to get this down a few thousand lines of code.
Loops are not unfamiliar to me as you could see if you peaked at my other modules I have codded.
But in the instance of this Sub I had a hard time seeing the logic to do what I needed to be done any other way then I did it.

I would post the code here but that would be insane as there is 14,900 lines of it.

While that is a lot of code. I promise it is all very easy to follow and straight forward what I am trying to do.
I will upload the Entire Workbook.
The sub I have issue with is
Module5
Sub UpdateDaily()

Basically what this does is it takes values from the first sheet and based upon what day of the week it is copies that data to the appropriate sheet under the appropriate day of the month.

Any help given would surely take my programming up a notch.
I thank you in advance for your time.

**StevenM** · 05-07-2012, 04:05 AM

(1) Never used "As Integer" change to "As Long"
Reason: Computers today find long (integers) easier to use than (short) integers.
"As Integer" creates a short integer.
"As Long" creates a long integer.

(2)

Please Login or Register  to view this content.

There is no reason to "Select" a cell before putting a value from a cell into an array, or before putting a value from an array into a cell.
That will almost cut your code in half.

(3)

Please Login or Register  to view this content.

Should be:

Please Login or Register  to view this content.

(4) I'm not going to copy your code, but I've copied the comments.
I then added the cells for each group. These cells are five rows apart.
And so:

Please Login or Register  to view this content.

Multiplication takes precedence over addition, so 5 + i * 5 is the same as 5 + (i * 5).
"N" & (5 + i * 5) when i = 0 creates a string "N5"
"N" & (5 + i * 5) when i = 7 creates a string "N40"

**StevenM** · 05-07-2012, 05:11 AM

I am not asking for some fish.
I am looking for some one to teach me how to fish here.

(5) You can loop through your Worksheets 2 through 9 with the following code:

Please Login or Register  to view this content.

Between the with & end with you can refer to a Range like so:
.Range("A1") = whatever or whatever = .Range("A1")

The dot before Range means connect with: wb.Worksheets(i)

For example:

Please Login or Register  to view this content.

The loop would take care of the left side of equals.

Please Login or Register  to view this content.

The right side of the equals might be:

Please Login or Register  to view this content.

"Z" & 4 + (i - 2) * 5 when i = 2 is a string "Z4"
"Z" & 4 + (i - 2) * 5 when i = 3 is a string "Z9"
etc.
Note: Range without a dot refers to the Active Worksheet.

**StevenM** · 05-07-2012, 05:38 AM

As an afterthought,

Please Login or Register  to view this content.

Can be simplified to:

Please Login or Register  to view this content.

**StevenM** · 05-07-2012, 06:08 AM

(5b)

Please Login or Register  to view this content.

**Marcol** · 05-07-2012, 07:04 AM

Does this do the same thing as your code?

Please Login or Register  to view this content.

**shg** · 05-07-2012, 08:29 AM

Or ...

Please Login or Register  to view this content.

I have 14,900 Lines of code in my Sub, Could someone show me how to be more efficient?

LinkBack

Thread Tools

Rate This Thread

Display

I have 14,900 Lines of code in my Sub, Could someone show me how to be more efficient?

Re: I have 14,900 Lines of code in my Sub, Could someone show me how to be more efficient?

Re: I have 14,900 Lines of code in my Sub, Could someone show me how to be more efficient?

Re: I have 14,900 Lines of code in my Sub, Could someone show me how to be more efficient?

Re: I have 14,900 Lines of code in my Sub, Could someone show me how to be more efficient?

Re: I have 14,900 Lines of code in my Sub, Could someone show me how to be more efficient?

Re: I have 14,900 Lines of code in my Sub, Could someone show me how to be more efficient?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions