Pastespecial xlvalues

[Redline]

I am using an add-in that has a spline() function to interpolate missing values from a range. It works fine on the computer that runs the reports, but the computer that people use the view the report doesnt have the add-in installed (and that is not an option).

To get around this, I thought I could copy/pastespecial (xlvalues) and save the worksheet so that only the actual values would remain, but each time I do that I get #NAME in the field.

Any thoughts how to show the physical results of the spline() function without leaving the formula?

This does NOT work, and I am left with the cells being literally '#NAME' instead of the results of the spline function;


For i = 2 To 24
Sheets("Hist").Cells(3, i + 2).Formula = "=Spline(Data!$A$2:$A$10,Data!$C$2:$C$10," & Chr(66 + i) & "2,TRUE)"
Next i

Sheets("Hist").Range("D3:Z3").Copy
Sheets("Hist").Range("D3").PasteSpecial xlValues

[p45cal]

try xlPasteValues instead of xlValues

[Kenneth Hobs]

Sheets("Hist").Range("D3:Z3").Value = Sheets("Hist").Range("D3:Z3").Value

[Zack Barresse]

Or just set the value to equal the Evaluate("your formula here"), skip the unnecessary step.

[Redline]

Thanks for all of your suggestions, although none of those worked out.

I am left with a value of #NAME for each of the above scenarios.

The only other reason I can think of is that this module is called via vbs script....

I think ill just write a cubic spline module so its available to anyone who opens the report.

Thanks for your time.

[p45cal]

Step through your macro using F8. At what line does the first instance of #NAME? appear?

[Kenneth Hobs]

Most likely your Spline() formula is the problem or your data is not what Spline() expects. Once you fix that, other methods posted here will set the values.

You could use debug.print to see where the problem occurs if you don't want to use the F8 method to debug.

Examine your results in your VBE Immediate Window after you play the code. Look at the string s which should be a valid Spline() formula.

e.g.

dim s as string
For i = 2 To 24
s = "=Spline(Data!$A$2:$A$10,Data!$C$2:$C$10," & Chr(66 + i) & "2,TRUE)"
debug.print i , s

Sheets("Hist").Cells(3, i + 2).Formula = "=Spline(Data!$A$2:$A$10,Data!$C$2:$C$10," & Chr(66 + i) & "2,TRUE)"
debug.print Sheets("Hist").Cells(3, i + 2).Value
Next i