Math calculations in VBA
 

Hi,

I'm trying to write some code to automatically calculate different math
functions based on a set of data. Namely: mean, std dev, and %rsd. The
calculations are based off of data that is imported and contains a variable
number of entries. The data is initially imported in a big unordered clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need to do
these calculations on each group of data. I have accomplished the grouping
by using looped counters that search for matches and then record the first
and last cell addresses to define the upper and lower limits of each group ie
A1:A21. This gives me the address locations, stored in variables, of the
data I want to calculate. My problem is all the examples of formula code in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my variables in
the equation they are not recognized due to the quotes. Can someone show me
the syntax to do what I need? I basically would like something like this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for example.

TIA,

Eric

Math calculations in VBA
 

Hi Eric,

ActiveCell.FormulaR1C1 = "AVERAGE("&FirstAdress&","&LastAdress&")"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Eric" wrote in message
...
Hi,

I'm trying to write some code to automatically calculate different math
functions based on a set of data. Namely: mean, std dev, and %rsd. The
calculations are based off of data that is imported and contains a
variable
number of entries. The data is initially imported in a big unordered
clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need to do
these calculations on each group of data. I have accomplished the
grouping
by using looped counters that search for matches and then record the first
and last cell addresses to define the upper and lower limits of each group
ie
A1:A21. This gives me the address locations, stored in variables, of the
data I want to calculate. My problem is all the examples of formula code
in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my variables
in
the equation they are not recognized due to the quotes. Can someone show
me
the syntax to do what I need? I basically would like something like this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for
example.

TIA,

Eric

Math calculations in VBA
 

Eric,

If First address and lastaddress have an A1 style string, you need

ActiveCell.Formula = "AVERAGE("&FirstAdress&","&LastAdress&")"
--
HTH

-------

Bob Phillips
"Niek Otten" wrote in message
...
Hi Eric,

ActiveCell.FormulaR1C1 = "AVERAGE("&FirstAdress&","&LastAdress&")"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Eric" wrote in message
...
Hi,

I'm trying to write some code to automatically calculate different math
functions based on a set of data. Namely: mean, std dev, and %rsd. The
calculations are based off of data that is imported and contains a
variable
number of entries. The data is initially imported in a big unordered
clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need to
do
these calculations on each group of data. I have accomplished the
grouping
by using looped counters that search for matches and then record the
first
and last cell addresses to define the upper and lower limits of each
group
ie
A1:A21. This gives me the address locations, stored in variables, of
the
data I want to calculate. My problem is all the examples of formula
code
in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my
variables
in
the equation they are not recognized due to the quotes. Can someone
show
me
the syntax to do what I need? I basically would like something like
this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for
example.

TIA,

Eric

Math calculations in VBA
 

Hi thanks for you input, unfortunately I'm still getting this error:

Compile Error: Expected end of statement. and the "," area is
highlighted. I've tried

ActiveCell.FormulaR1C1 = "=AVERAGE("&FirstOccurrence&","&LastOccurrence &")"
ActiveCell.FormulaR1C1 = "AVERAGE("&FirstOccurrence&","&LastOccurrence& ")"
ActiveCell.FormulaR1C1 = "=AVERAGE("&FirstOccurrence&":"&LastOccurrence &")"

I keep getting the same error, what am I doing wrong? Thanks again,

Eric


"Niek Otten" wrote:

Hi Eric,

ActiveCell.FormulaR1C1 = "AVERAGE("&FirstAdress&","&LastAdress&")"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Eric" wrote in message
...
Hi,

I'm trying to write some code to automatically calculate different math
functions based on a set of data. Namely: mean, std dev, and %rsd. The
calculations are based off of data that is imported and contains a
variable
number of entries. The data is initially imported in a big unordered
clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need to do
these calculations on each group of data. I have accomplished the
grouping
by using looped counters that search for matches and then record the first
and last cell addresses to define the upper and lower limits of each group
ie
A1:A21. This gives me the address locations, stored in variables, of the
data I want to calculate. My problem is all the examples of formula code
in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my variables
in
the equation they are not recognized due to the quotes. Can someone show
me
the syntax to do what I need? I basically would like something like this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for
example.

TIA,

Eric

Math calculations in VBA
 

FirstAdress = "A1"
LastAdress = "A10"
ActiveCell.Formula = "=AVERAGE(" & FirstAdress & "," & LastAdress & ")"

--
Regards,
Tom Ogilvy


"Eric" wrote in message
...
Hi,

I'm trying to write some code to automatically calculate different math
functions based on a set of data. Namely: mean, std dev, and %rsd. The
calculations are based off of data that is imported and contains a
variable
number of entries. The data is initially imported in a big unordered
clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need to do
these calculations on each group of data. I have accomplished the
grouping
by using looped counters that search for matches and then record the first
and last cell addresses to define the upper and lower limits of each group
ie
A1:A21. This gives me the address locations, stored in variables, of the
data I want to calculate. My problem is all the examples of formula code
in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my variables
in
the equation they are not recognized due to the quotes. Can someone show
me
the syntax to do what I need? I basically would like something like this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for
example.

TIA,

Eric

Math calculations in VBA
 

You need to put in spaces as I showed you in my post.

ActiveCell.FormulaR1C1 = "=AVERAGE(" & _
FirstOccurrence & "," & LastOccurrence & ")"



--
Regards,
Tom Ogilvy

"Eric" wrote in message
...
Hi thanks for you input, unfortunately I'm still getting this error:

Compile Error: Expected end of statement. and the "," area is
highlighted. I've tried

ActiveCell.FormulaR1C1 =
"=AVERAGE("&FirstOccurrence&","&LastOccurrence &")"
ActiveCell.FormulaR1C1 = "AVERAGE("&FirstOccurrence&","&LastOccurrence& ")"
ActiveCell.FormulaR1C1 =
"=AVERAGE("&FirstOccurrence&":"&LastOccurrence &")"

I keep getting the same error, what am I doing wrong? Thanks again,

Eric


"Niek Otten" wrote:

Hi Eric,

ActiveCell.FormulaR1C1 = "AVERAGE("&FirstAdress&","&LastAdress&")"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Eric" wrote in message
...
Hi,

I'm trying to write some code to automatically calculate different
math
functions based on a set of data. Namely: mean, std dev, and %rsd.
The
calculations are based off of data that is imported and contains a
variable
number of entries. The data is initially imported in a big unordered
clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need
to do
these calculations on each group of data. I have accomplished the
grouping
by using looped counters that search for matches and then record the
first
and last cell addresses to define the upper and lower limits of each
group
ie
A1:A21. This gives me the address locations, stored in variables, of
the
data I want to calculate. My problem is all the examples of formula
code
in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my
variables
in
the equation they are not recognized due to the quotes. Can someone
show
me
the syntax to do what I need? I basically would like something like
this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for
example.

TIA,

Eric

Math calculations in VBA
 

Eric,

check m y response too

--
HTH

Bob Phillips

"Eric" wrote in message
...
Hi thanks for you input, unfortunately I'm still getting this error:

Compile Error: Expected end of statement. and the "," area is
highlighted. I've tried

ActiveCell.FormulaR1C1 =
"=AVERAGE("&FirstOccurrence&","&LastOccurrence &")"
ActiveCell.FormulaR1C1 = "AVERAGE("&FirstOccurrence&","&LastOccurrence& ")"
ActiveCell.FormulaR1C1 =
"=AVERAGE("&FirstOccurrence&":"&LastOccurrence &")"

I keep getting the same error, what am I doing wrong? Thanks again,

Eric


"Niek Otten" wrote:

Hi Eric,

ActiveCell.FormulaR1C1 = "AVERAGE("&FirstAdress&","&LastAdress&")"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Eric" wrote in message
...
Hi,

I'm trying to write some code to automatically calculate different
math
functions based on a set of data. Namely: mean, std dev, and %rsd.
The
calculations are based off of data that is imported and contains a
variable
number of entries. The data is initially imported in a big unordered
clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need
to do
these calculations on each group of data. I have accomplished the
grouping
by using looped counters that search for matches and then record the
first
and last cell addresses to define the upper and lower limits of each
group
ie
A1:A21. This gives me the address locations, stored in variables, of
the
data I want to calculate. My problem is all the examples of formula
code
in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my
variables
in
the equation they are not recognized due to the quotes. Can someone
show
me
the syntax to do what I need? I basically would like something like
this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for
example.

TIA,

Eric

Math calculations in VBA
 

Thanks so much! Syntax is evil ;-)

"Tom Ogilvy" wrote:

You need to put in spaces as I showed you in my post.

ActiveCell.FormulaR1C1 = "=AVERAGE(" & _
FirstOccurrence & "," & LastOccurrence & ")"



--
Regards,
Tom Ogilvy

"Eric" wrote in message
...
Hi thanks for you input, unfortunately I'm still getting this error:

Compile Error: Expected end of statement. and the "," area is
highlighted. I've tried

ActiveCell.FormulaR1C1 =
"=AVERAGE("&FirstOccurrence&","&LastOccurrence &")"
ActiveCell.FormulaR1C1 = "AVERAGE("&FirstOccurrence&","&LastOccurrence& ")"
ActiveCell.FormulaR1C1 =
"=AVERAGE("&FirstOccurrence&":"&LastOccurrence &")"

I keep getting the same error, what am I doing wrong? Thanks again,

Eric


"Niek Otten" wrote:

Hi Eric,

ActiveCell.FormulaR1C1 = "AVERAGE("&FirstAdress&","&LastAdress&")"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Eric" wrote in message
...
Hi,

I'm trying to write some code to automatically calculate different
math
functions based on a set of data. Namely: mean, std dev, and %rsd.
The
calculations are based off of data that is imported and contains a
variable
number of entries. The data is initially imported in a big unordered
clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need
to do
these calculations on each group of data. I have accomplished the
grouping
by using looped counters that search for matches and then record the
first
and last cell addresses to define the upper and lower limits of each
group
ie
A1:A21. This gives me the address locations, stored in variables, of
the
data I want to calculate. My problem is all the examples of formula
code
in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my
variables
in
the equation they are not recognized due to the quotes. Can someone
show
me
the syntax to do what I need? I basically would like something like
this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for
example.

TIA,

Eric

Math calculations in VBA
 

Can you show how this would work using integer values for row, column to
reference the cell range for the calculation?

TIA,

Tim

"Bob Phillips" wrote:

Eric,

If First address and lastaddress have an A1 style string, you need

ActiveCell.Formula = "AVERAGE("&FirstAdress&","&LastAdress&")"
--
HTH

-------

Bob Phillips
"Niek Otten" wrote in message
...
Hi Eric,

ActiveCell.FormulaR1C1 = "AVERAGE("&FirstAdress&","&LastAdress&")"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Eric" wrote in message
...
Hi,

I'm trying to write some code to automatically calculate different math
functions based on a set of data. Namely: mean, std dev, and %rsd. The
calculations are based off of data that is imported and contains a
variable
number of entries. The data is initially imported in a big unordered
clump.
I have already written code to sort and then segregate the data into
different groups depending on what it comes from. After this I need to
do
these calculations on each group of data. I have accomplished the
grouping
by using looped counters that search for matches and then record the
first
and last cell addresses to define the upper and lower limits of each
group
ie
A1:A21. This gives me the address locations, stored in variables, of
the
data I want to calculate. My problem is all the examples of formula
code
in
VBA I have found uses quotes and defined cell addresses to work ie:
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-4]C:R[-1]C)". If I put my
variables
in
the equation they are not recognized due to the quotes. Can someone
show
me
the syntax to do what I need? I basically would like something like
this:

Cells(1, 20).Select
ActiveCell.FormulaR1C1 = AVERAGE(FirstAddress, LastAddress)

where first and last address have stored a location A1 and A19 for
example.

TIA,

Eric