Hi

I'm very much a novice at VBA and have created a macro which includes a
formula to calculate a sales parameter. However as the number of rows in the
range will change month on month I need to alter the cell range that the
formula is pasted into. Is this possible?

One way is to set a range object to the used range.

Dim rng As Range

Set rng = Range("A1", Range("A1").End(xlDown))
rng.Formula = "=B1"

--
HTH

Bob Phillips

"Dave L" wrote in message
...
Hi

I'm very much a novice at VBA and have created a macro which includes a
formula to calculate a sales parameter. However as the number of rows in
the
range will change month on month I need to alter the cell range that the
formula is pasted into. Is this possible?

Thanks Bob

Bob Phillips wrote:
One way is to set a range object to the used range.

Dim rng As Range

Set rng = Range("A1", Range("A1").End(xlDown))
rng.Formula = "=B1"

Hi

I'm very much a novice at VBA and have created a macro which includes a
formula to calculate a sales parameter. However as the number of rows in the
range will change month on month I need to alter the cell range that the
formula is pasted into. Is this possible?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200507/1

One possible solution asuming that your starts in A3 :

Sub Macro1()
'
Dim MyRange As Range

Set MyRange = Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp))

End Sub

If you want to extand the range to include colums B, C, D and E as well
replace
Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp)) with
Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 4))

"Dave L" wrote in message
...
Hi

I'm very much a novice at VBA and have created a macro which includes a
formula to calculate a sales parameter. However as the number of rows in
the
range will change month on month I need to alter the cell range that the
formula is pasted into. Is this possible?

alf bryn wrote:
One possible solution asuming that your starts in A3 :

Sub Macro1()
'
Dim MyRange As Range

Set MyRange = Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp))

End Sub

If you want to extand the range to include colums B, C, D and E as well
replace
Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp)) with
Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 4))

Hi

[quoted text clipped - 3 lines]
range will change month on month I need to alter the cell range that the
formula is pasted into. Is this possible?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200507/1

Tanks Alf

alf bryn wrote:
One possible solution asuming that your starts in A3 :

Sub Macro1()
'
Dim MyRange As Range

Set MyRange = Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp))

End Sub

If you want to extand the range to include colums B, C, D and E as well
replace
Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp)) with
Range(Range("A3"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 4))

Hi

[quoted text clipped - 3 lines]
range will change month on month I need to alter the cell range that the
formula is pasted into. Is this possible?


--
Message posted via http://www.officekb.com