variable range
 

hey !!!
please help ...
1).
I have recorded a macro which calculates average of some sample.
it uses the formula "=AVERAGE(A1:AD1)" (average of 30 data)
when I run the macro next time I have only 20 data in the sample.
I want macro to calculate it as "=AVERAGE(A1:T1)"
How to make the range to be variable?

2).
I calculated the average for the first row, Then I pasted the value
for next 2000 cells.
here are the code lines ..

ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!R[-1]C:R[-1]C[29])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A2001")
Type:=xlFillDefault

next time when I run the macro I have only 1000 simulations and I wan
to autofill the range as "A2:A1001".
but with the recorded macro it is autofilling till A2001.
How to put variable in this ?

Thanks in advance...
--rajendr

--
rsank
-----------------------------------------------------------------------
rsankh's Profile: http://www.msusenet.com/member.php?userid=187
View this thread: http://www.msusenet.com/t-187051322

variable range
 

I will choose to answer part two... :-), the answer to part 1 is similar.


One choice is to Dimension a range variable and you can set its value by
letting the user enter it in a inputbox.

Your code:
ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!R[-1]C:R[-1]C[29])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A2001"),
Type:=xlFillDefault

can be changed to this code:

ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!R[-1]C:R[-1]C[29])"
Range("A2").Select

Dim MyRange As Range
Set MyRange = ActiveSheet.Range("Sheet1!" & _
InputBox("Input the range in format shown:", , "A2:A1001"))
Selection.AutoFill Destination:=MyRange, _
Type:=xlFillDefault



"rsankh" wrote in message
...

hey !!!
please help ...
1).
I have recorded a macro which calculates average of some sample.
it uses the formula "=AVERAGE(A1:AD1)" (average of 30 data)
when I run the macro next time I have only 20 data in the sample.
I want macro to calculate it as "=AVERAGE(A1:T1)"
How to make the range to be variable?

2).
I calculated the average for the first row, Then I pasted the values
for next 2000 cells.
here are the code lines ..

ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!R[-1]C:R[-1]C[29])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A2001"),
Type:=xlFillDefault

next time when I run the macro I have only 1000 simulations and I want
to autofill the range as "A2:A1001".
but with the recorded macro it is autofilling till A2001.
How to put variable in this ?

Thanks in advance...
--rajendra


--
rsankh
------------------------------------------------------------------------
rsankh's Profile: http://www.msusenet.com/member.php?userid=1879
View this thread: http://www.msusenet.com/t-1870513229

variable range
 

rajendra,

1. The Average function ignores text, logical values and empty cells,
so if you clear the row each time, you won't need a dynamic range.

2. Use a variable that contains the number of simulations...
Dim lngSims as Long
lngSims = 1001
....
Selection.AutoFill Destination:=Range("A2",Cells(lngSims,1))

Jim Cone
San Francisco, USA


"rsankh" wrote in message
...

hey !!!
please help ...
1).
I have recorded a macro which calculates average of some sample.
it uses the formula "=AVERAGE(A1:AD1)" (average of 30 data)
when I run the macro next time I have only 20 data in the sample.
I want macro to calculate it as "=AVERAGE(A1:T1)"
How to make the range to be variable?

2).
I calculated the average for the first row, Then I pasted the values
for next 2000 cells.
here are the code lines ..

ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet1!R[-1]C:R[-1]C[29])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A2001"),
Type:=xlFillDefault

next time when I run the macro I have only 1000 simulations and I want
to autofill the range as "A2:A1001".
but with the recorded macro it is autofilling till A2001.
How to put variable in this ?

Thanks in advance...
--rajendra