Math calculations in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
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...
 
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...
 
FirstAdress = "A1"
LastAdress = "A10"
ActiveCell.Formula = "=AVERAGE(" & FirstAdress & "," & LastAdress & ")"
 
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...
 
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...
 
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...
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to run all variable combinations in a calculation model and store result 6
Using Two Variables in Excel Calculation VBA 1
Insert text in column A and calculate the average from column B 4
Help with my VBA and formula 8
Insert Formula in cells from VBA 2
Create Detail Sheets from Pivot Table 1
Excel How to code in excel vba 2007 using absolute refernce for cells 1
Run-time error '1004': Application defined or Object defined error 2

Back
Top