Why do Macros work sometimes?

[ctmurray]

The last few weeks I have been visiting this board for help with a macro (thanks). But the macro seems to work one week and not the next. Probably most is due to my poor programming, but it still amazes me that each week I have to go in and "fix" a macro that worked last week.

Two weeks ago the macro worked. Then it stopped working the next time I ran. The code was all in the "This Workbook" sheet and the forum suggested I move the code to a module. I did and it worked again.

This week the same macro gags on:

Please Login or Register  to view this content.

yet this piece of code has worked for many weeks. Suddenly now it does not work. Below is my change that is working "so far". I understand now it is probably better to be quite specific with what cells to select. Yet the code worked for a long time.

Please Login or Register  to view this content.

for the details of my current issue.

I am a novice coder and I have borrowed and cobbled together the code I have. The purpose of my macros is to automate a tedious and long repeated analsyis of some data that updates each week. I would love to have the macro actually work without further tweeking.

Are there any other dumb, beginner mistakes in the cross posted code? General rules of thumb like "always put ActivesSheet before commands like Cells"?

Thanks

Cam

[shg]

Why would we want to go to another forum to find the details of your question?

[ctmurray]

Actually the cross post is to this forum - I probably used the wrong term in my description. I just did not want to re-post the same details. Sorry for the confusion.

Cam

[Paul]

Hi ctmurray,

Try using this code. It's a little bit more streamlined and shouldn't run afowl of sheet references or result in range/cell errors.

Please Login or Register  to view this content.

I commented out the variables 'start', 'pvalue', 'rvalue' and 'svalue' since they weren't used anywhere in the code. If they're used elsewhere in the module, uncomment them and declare their data type (Integer, String, etc.)

[ctmurray]

Thanks for the code, I will use and read over to learn some better coding skills (which I need). Some of the dimensioned items were extra, probably left over from code I cut. I think I got most this code from somewhere else. As I was troubleshooting this code I noticed that none of the dimensioned items were specified as intergers (or anything else). So I did make the ones in use into intergers.

Thanks again.

[Norie]

Cam

I'm pretty sure the problem has absolutely nothing to do with how you've dimensioned your variables.

The issue is more likely to be with unqualified references and/or the use of select.

Firstly if you have something like this:

Please Login or Register  to view this content.

Then it will refer to cells on what VBA considers the active sheet, which may or may not actually be the one you want to refer to.

Secondly there is normally no need to use Select and in fact it can cause problems.

There's also no need to use Activate as long as you properly reference everything and you should use Long not Integer.

[ctmurray]

I appreciate the comment.

I am adopting the code provided pjoaquin which does not use the Cell.Select function. But why does select not work in general?

Why use long vs integer?

Cam

[Norie]

Cam

I've not had time to look at your original code.

But to answer your questions.

1 You don't need to use select.

2 VBA will automatically convert Integer to Long, so if you use Integer it may have some impact on performance.