How do I insert an interger into a range?

[ratdogexcel]

Gday, how do I replace the 1000 with the value that HCCount returns?

There might be an easier way

All I want to do is copy B2 down the col to the amount stated in a cell A1 on another sheet

Please Login or Register  to view this content.

And if the A1 was on another sheet how would i ask it to look there?


cheers

[HaHoBe]

Hi, ratdogexcel,

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

[humdingaling]

Please Login or Register  to view this content.

if A1 is another sheet add sheet name in front

[ratdogexcel]

Perfect thanks a heap Humdingaling and everyone else


Does the range reset when the sub ends? Or when i set the range again in another sub?

[HaHoBe]

Hi, ratdogexcel,

according to Microsoft the scope of rng ends at the end of the macro in which it is defined. Furthermore it is declared as a local variable to work with so no other macro will know about that (if nopt passed as parameter).

But I wonder why you would need to use 2 variables for performing an action that could be done without any variables at all (I try to avoid unnecessary variables as well as objects but if used try to clean up at the ened of the macro).

Ciao,
Holger

[ratdogexcel]

Thanks for the explanation

I need to define the variable for each sheet.

I perform the same action on multiple sheets but they relate to a different A1 each time.

[HaHoBe]

Hi, ratdogexcel,

I need to define the variable for each sheet.

Wrap the code you have in a loop like

Please Login or Register  to view this content.

and make the code relate to ws in order to get it to work on every sheet.

...but they relate to a different A1 each time

Hard to help if you donīt mention on which system this is build.

Ciao,
Holger