Macro only works step-by-step

Hi everyone. I have a macro that works step-by-step but not when run fully. I found the portion of the code where the issue lies. The value for count needs to be equal to 3 in this specific run of the macro. I checked and count does in fact equal 3, however, it is behaving as if count was equal to 1. When I replace count with the number 3 in my code, it works. Can anyone explain this to me?

Please Login or Register  to view this content.

Hello smw10c,

The code you have posted looks OK and the only thing that can make this code work "as if count was equal to 1" is that your loop condition is only valid for one run.
You might want to attach your sample sheet and get deeper into what you want to achieve and what is happening instead upon running the macro.

Regards,
Buskolsky

This is not a macro but a fragment of it, this is evidenced by the lack of: header and end of procedure, variable declarations (rng1, Arr/Arr(), count) and occurrence an excessive, redundant "End If" clause - by the way: "Count" is a "component" of the vba language, see contextual help ("Count" Property/collection). What does the data layout on which this code is to work look like ?

Hi there,

Firstly, you have an unpaired End If statement in your code, but I'll assume that the corresponding If statement just "fell by the wayside" when you were copying your actual code to the post.

Secondly (and more importantly), this statement will fail if Sheet(2) is not the active sheet when the code is running, but this failure will be hidden by the On Error Resume Next statement in the preceding line. This can be overcome by inserting the highlighted line.

In general you should always include an On Error GoTo 0 (or equivalent) statement as close as possible to the On Error Resume Next statement - this will ensure that errors occurring later in the code are not unintentionally hidden.

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

First off, thank you everyone. I don't want to blow up your notifications by responding to each and every one. Richard and Greg were spot on. This completely worked. If you don't mind me asking, why does it need to be activated at that point in time?

Hi again,

Many thanks for your very prompt feedback and also for the Reputation increase - much appreciated.

The ActiveCell is a child element of the ActiveSheet (not all that surprising really! ), so if you don't activate the specific worksheet you need, the ActiveCell will be taken from whichever worksheet happens to be active when the code is running.

I fully agree with Richard's comment and have found this to be the case in many of the requests for help here. In fact, when I read "it works on a step-by-step basis" I immediately start looking for references which are not fully qualified, and in 99.9% of cases I find them

Hope this helps,

Best regards,

Greg M