Excel VBA, dynamic references

[hl]

I would like the macro to copy a static range and paste it in spesific
rows depending on counter-formulas.

I have one counter in cell av1, at the time giving the result 331.

So for pasting in one cell, I change the recorded VBA-string:
Range("AV331").Select

into the following that works fine:
Range("AV" & Range("Av1").Value).Select

My problem is that I also have a counter in aw1, at the time giving
the result 333. So what I want to do is to paste in a range, not in a
single cell:
Range("AV331:AV333").Select

How do I do this? Tried:
Range("AV" & Range("Av1").Value:"AV" & Range("Av1").Value).Select

but did not work. Any ideas?

Brgds,
Harald

 

[FSt1]

hi
not entirely sure what you trying to do but here is some sample code to
select a range.....

Dim r As Range
Set r = Range("A1:C1")
r.Select

regards
FSt1

 

[hl]

Hi,

I want the range AV331:AV333 to be dynamic, meaning the rownumbers
should be based on the result in the counter-cells AV1 and AW1.

In your example, Range("A1:C1") will always be selected as it is not
connected to a dynamic cell if I am not wrong.

 

[Nattasiray]

Dear (e-mail address removed)

You must change the code to:
Range("AV" & [Av1]&":AV" & [Av1]).PasteSpecial xlPasteValues

[] Is Evaluate Method

 

[hl]

Dear (e-mail address removed)

You must change the code to:
Range("AV" & [Av1]&":AV" & [Av1]).PasteSpecial xlPasteValues

[] Is Evaluate Method

Thanks for the reply but still gets error from VBA. What do you mean
by [] Is Evaluate Method? Do I need to put that in the string?

Brgds,
Harald

 

[Jon Peltier]

Don't be trapped into using []. It is a handy shortcut, but it's more useful
to keep the full referencing of the object and its methods in the code:

Range("AV" & Range("AV1").Value & ":AV" & Range("AV1").Value).Select

although this is a one-celled reference the way it is written. Perhaps you
mean a variation like one of these:

Range("AV" & Range("AV1").Value & ":AV" & Range("AV2").Value).Select

Range("AV" & Range("AV1").Value & ":AZ" & Range("AV1").Value).Select

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Dear (e-mail address removed)

You must change the code to:
Range("AV" & [Av1]&":AV" & [Av1]).PasteSpecial xlPasteValues

[] Is Evaluate Method

Thanks for the reply but still gets error from VBA. What do you mean
by [] Is Evaluate Method? Do I need to put that in the string?

Brgds,
Harald

 

[hl]

Tthat worked fine.

And yes, I misprinted it to be a one-celled reference in my original
question. It now is as follows:
Range("AV" & Range("AV1").Value & ":AV" & Range("AW1").Value).Select

Thanks!

Harald