Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA, dynamic references
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA, dynamic references
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 " wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA, dynamic references
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA, dynamic references
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA, dynamic references
On 26 Nov, 11:58, Nattasiray
wrote: Dear 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA, dynamic references
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 _______ wrote in message ... On 26 Nov, 11:58, Nattasiray wrote: Dear 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel VBA, dynamic references
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic formulas including worksheet references | Excel Worksheet Functions | |||
dynamic cell references | Excel Worksheet Functions | |||
Dynamic references in diagram source data? | Excel Discussion (Misc queries) |