small data sets are buggering range.select

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

Guest

I am using coding to automate some charts. It works great unless my data set
has fewer than 25 lines. When the data set is small (<25 rows) the code
interrupts. Debugging takes me to:
ActiveCell.FormulaR1C1 = "=average(RC2:r[-" & mysmooth - 1 & "]c2)"

the section of coding that this applies to is:
'determine smoothing

Range("f20").Select
ActiveCell.Formula = "=LOOKUP(" & numberrows & ",'[Trending Source
rework 2.xls]Stats'!R1C1:R5C1,'[Trending Source rework
2.xls]Stats'!R1C2:R5C2)"
mysmooth = ActiveCell.Value

Range("F21").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""PI Smoothing ("",smooth,""
Point)"")"
Range("h" & mysmooth + 2).Select
ActiveCell.FormulaR1C1 = "=average(RC2:r[-" & mysmooth - 1 & "]c2)"
Selection.AutoFill Destination:=Range("h" & mysmooth + 2 & ":h" &
numberrows), Type:=xlFillDefault
Columns("h").EntireColumn.AutoFit

The table on the STATs sheet is in A1:b5
0 0
25 4
50 6
150 12
250 18

Anything over 25 rows it works fine.

Any ideas?
TIA
 
Excel has a problem when the result of the expression "mysmooth - 1" equals a
negative number. When your data set < 25, mysmooth = 0. When mysmooth = 0,
you end up with r[--1] as the row reference in the second cell of the average
range.

To avoid the double minus sign problem, change your average funciton to read:
ActiveCell.FormulaR1C1 = "=average(RC2:r[" & -(mysmooth - 1) & "]c2)"

Excel correctly evaluates the expression -(0-1) as as the value + 1 (the row
reference = [r1] ) and does not return an error when VBA writes the function.

I hope this helps.
-stanshoe
 

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

Back
Top