variable in worksheet function average

[Bill Roberts]

my code is activecell.formulaR1C1="=average(R[-7]C[-2]:RC[-2])"
but I want to make the "7" a variable "a"
activecell.formulaR1C1="=average(R[a]C]-2]:RC[-2])"
Is there a proper Dimension statement for "a" (integer, variant, etc.) that
will allow me to do this? If not, can anyone give me the code I should use?
TIA

 

[Jim Cone]

Dim a As Double
a = -7
ActiveCell.FormulaR1C1 = "=average(R[" & a & "]C[-2]:RC[-2])"
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL add-in

..
..
..

"Bill Roberts" <[email protected]>
wrote in message
my code is activecell.formulaR1C1="=average(R[-7]C[-2]:RC[-2])"
but I want to make the "7" a variable "a"
activecell.formulaR1C1="=average(R[a]C]-2]:RC[-2])"
Is there a proper Dimension statement for "a" (integer, variant, etc.) that
will allow me to do this? If not, can anyone give me the code I should use?
TIA

 

[Jim Rech]

Personally I'd use a Long, but to each his own.

 

[Bill Roberts]

I tried "integer" and it seemed OK. Thanks for the help. I would have gone
a long time before I found that. I have 4 books on VBA, and tried on line
help, etc. This forum is great!! Is there any possibility you can suggest
a link or a help line that would explain using the ampersand in some detail??
TIA
--
Bill Roberts

Personally I'd use a Long, but to each his own.

my code is activecell.formulaR1C1="=average(R[-7]C[-2]:RC[-2])"
but I want to make the "7" a variable "a"
activecell.formulaR1C1="=average(R[a]C]-2]:RC[-2])"
Is there a proper Dimension statement for "a" (integer, variant, etc.)
that
will allow me to do this? If not, can anyone give me the code I should
use?
TIA

.

 

[Jim Rech]

The reason I would use a Long versus an Integer is that the integer positive
range goes up to 32k while an Excel sheet will have 64k rows in Excel 2003
and 1000k in 2007 and 2010. So you'd get an overflow error if you used a
row greater than 32767. Of course you probably wouldn't but it's best to
program for the worst case. I must confess the odd Integer dim has come
back to bite me over the years.

The ampersand has a variety of uses in VBA; I don't know of a resource that
details them.

I tried "integer" and it seemed OK. Thanks for the help. I would have
gone
a long time before I found that. I have 4 books on VBA, and tried on line
help, etc. This forum is great!! Is there any possibility you can
suggest
a link or a help line that would explain using the ampersand in some
detail??
TIA
--
Bill Roberts

Personally I'd use a Long, but to each his own.

my code is activecell.formulaR1C1="=average(R[-7]C[-2]:RC[-2])"
but I want to make the "7" a variable "a"
activecell.formulaR1C1="=average(R[a]C]-2]:RC[-2])"
Is there a proper Dimension statement for "a" (integer, variant, etc.)
that
will allow me to do this? If not, can anyone give me the code I should
use?
TIA

.

 

[Cameron Farquharson]

I tried "integer" and it seemed OK. Thanks for the help. I would have gone
a long time before I found that. I have 4 books on VBA, and tried on line
help, etc. This forum is great!! Is there any possibility you can suggest
a link or a help line that would explain using the ampersand in some detail??
TIA