Make loop run faster

Hi all. I need to see if I can make this loop any faster, but first, I have a question regarding this. I don't know why I did

Please Login or Register  to view this content.

What does that mean? I just want to know when to use the "!" .. If I define any of those as an Integer or a Long, it rounds the final answer to a whole number...so I have to use the above in order for the loop to work correctly. Why is that?

Anyway, here is the loop. If someone can tell me how to make it run faster, I'd really appreciate that. Thank you!

Please Login or Register  to view this content.

Integer and Long are both whole numbers. But you can use Double if you need to allow decimal values.

No, ! is a single. So as not to confuse users, just dimesion variables using words.

Please Login or Register  to view this content.

etc.

There's also no reason to dimesion a variable as Integer, since it will be converted to Long automatically at runtime. Just dimension the variable as Long to start with.

Also, please take a moment to read the forum rules and in the future wrap all code in between code tags like you see above.

[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

Thank you.

Originally Posted by losmi8

IF I don't Dim anything, everything is treated as a Long? If I don't Dim anything, everything seems to work just fine. Could this cause problems?

While your macros will execute and run normally, not Dim-ing variables means they will all be dimensioned as type Variant (not Long), which uses the most memory of any standard variable type since it can be used to contain any type of variable. It also can minutely slow down your code if there are a lot of variables that need their types determined. Dim all variables as their proper type is just good practice, and use a good naming convention.

Along with declaring your variables, you may want to put Option Explicit at the beginning of all code/sheet modules. This will ensure that all variables must be Dim-ed, and that all variables are typed correctly within your code - if you accidentally type 'lastRow' in one area and 'lastRw' elsewhere, and you did not Dim a 'lastRw' variable, the procedure will not run and generate an error.

What you're doing is fine if you understand it and it works the way it's supposed to every time...

As for "can it be better", the answer is almost always yes. To get really good suggestions on how to do it better, post up a workbook showing sample data, and BEFORE/AFTER sheets so it's completely clear what you want to do.

Different eyes looking at the same task may spot significantly more "efficient" approaches.

Is EMAp always 10?
If EMAp isn't always 10, can the value of EMAp be stored in a cell on the worksheet?
Do you always start i = 2 to LR - EMAp?
Do you always start j = 1 to EMAp?

It might be best to provide a sample workbook with data. Also, can you explain, in words, what you're trying to accomplish with the For/Next loop(s)? It looks like you're trying to loop through cells in column G over and over and eventually coming up with a sum.