Math calculations in VBA

E

Eric

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
 
N

Niek Otten

Hi Eric,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
B

Bob Phillips

Eric,

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

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

-------

Bob Phillips
Niek Otten said:
Hi Eric,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


Eric said:
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
Click to expand...
Click to expand...
 
E

Eric

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 said:
Hi Eric,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


Eric said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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

Tom Ogilvy

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

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



--
Regards,
Tom Ogilvy

Eric said:
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 said:
Hi Eric,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


Eric said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

Eric,

check m y response too

--
HTH

Bob Phillips

Eric said:
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 said:
Hi Eric,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


Eric said:
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
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

Thanks so much! Syntax is evil ;-)

Tom Ogilvy said:
You need to put in spaces as I showed you in my post.

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



--
Regards,
Tom Ogilvy

Eric said:
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 said:
Hi Eric,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


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
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Allard

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 said:
Eric,

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

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

-------

Bob Phillips
Niek Otten said:
Hi Eric,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


Eric said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top