Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

help with making macro with user input

  Asked By: Meenachi    Date: Dec 09    Category: MS Office    Views: 1080
  

I'm trying make a macro to automatically calculate the mean, standard deviation,
and
standard error of the mean for various rows of data. I can get a macro to work
for a set
number of rows, for example nine rows. However, I'd like to be able to input
different
numbers of rows.

I'm stuck when I try to use a variable in one part of the macro. I'm sure it's
just a simple
syntax problem.

My macro that works is the following

Sub MeanSDSEM9()
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[8]C[-1])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=STDEV(RC[-2]:R[8]C[-2])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/SQRT(9)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

I tried to add a user input in place of the 8 and 9 values in lines 2, 4 and 6.
For example

Sub MeanSDSEM()
x = InputBox("Enter number of samples")
->ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[x - 1]C[-1])"<-
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=STDEV(RC[-2]:R[x - 1]C[-2])"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/SQRT(x)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

I keep getting a runtime 1004 error on line 3 (marked with arrows). What is the
correct
syntax or is there a better way to do this?

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Harriet Ward     Answered On: Dec 09

Try:
Sub MeanSDSEM()
x = InputBox("Enter number  of samples")
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-1]:R[" & x - 1 & "]C[-1])"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=STDEV(RC[-2]:R[" & x - 1 & "]C[-2])"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]/SQRT(" & x & ")"
ActiveCell.Offset(1, 0).Select
End Sub

 
Answer #2    Answered By: Darwishi Massri     Answered On: Dec 09

I assume the & & designates a variable  in formula string?

 
Answer #3    Answered By: Binge Fischer     Answered On: Dec 09

> I assume the & & designates a variable  in formula string?

& is the concatenation operator and is used to join strings.

"=AVERAGE(RC[-1]:R[" & x - 1 & "]C[-1])"

Would give you a string made up of:
the string "=AVERAGE(RC[-1]:R["
the value of x - 1
and
the string "]C[-1])"

 
Didn't find what you were looking for? Find more on help with making macro with user input Or get search suggestion and latest updates.




Tagged: