1. Parsing trendline equation

Hi
I have written a macro to parse a trendline equation (peak area vs concentration) so I can use it to calculate chemical concentrations from area data. What I have written does the job for a single trendline equation on a spreadsheet. However, when I run the macro on a second trendline equation on the same sheet, the first set of extracted coefficients becomes the same as the second set of coefficients. This is because my macro places a formula in the cell I want the coefficient to be in, not a value (see attached JPG). Is there some way that I can convert the extracted coefficient from a formula to a value?
Thanks
Arjay

2. To use values instead of formulas, your code could be written like:
Code:
```Sub Parse_Coeffs()
Dim str As String
With ActiveCell
str = .Value
If Left(str, 1) = "-" Then
.Offset(1, 0) = Left(str, 10)
.Offset(2, 0) = Mid(str, 13, 12)
.Offset(3, 0) = Mid(str, 27, 12)
Else
.Offset(1, 0) = Left(str, 9)
.Offset(2, 0) = Mid(str, 12, 12)
.Offset(3, 0) = Mid(str, 26, 12)
End If
End With
End Sub```
This puts the calc in VBA and just adds the values into the cells, instead of putting the formulas into the cells.

Steve

3. The Following User Says Thank You to sdckapr For This Useful Post:

arjay13 (2012-12-10)

4. Now that Steve did the heavy lifting...

Code:
```Sub Parse_Coeffs()

Dim str   As String

With ActiveCell
str = .Value
If Left(str, 1) = "-" Then iAdj = 1
.Offset(1, 0) = Left(str, 9 + iAdj)
.Offset(2, 0) = Mid(str, 12 + iAdj, 12)
.Offset(3, 0) = Mid(str, 26 + iAdj, 12)
End With

End Sub```

5. Sdckapr
Many thanks for the prompt reply as well as the tidy code. It works better and cleaner than what I had ginned up. I discovered that if I copy the entire trendline equation including the "=" (accounting for the added characters in the "left" and "mid" commands), the macro doesn't get the exponents and leading signs correct with a leading minus sign after the "=". If I leave out the "=" sign and carefully cut the equation, it works perfectly!
Thanks
Arjay

6. Retiredgeek
Arjay

7. Another option, instead of copying the trendline, is to use LINEST to calculate the coefficients. You then get them "live" if any values change...

Steve

8. Steve
Yes, I usually use linest for the bulk of my work but I occasionally find it more convenient to just grab the regression equation from the chart for a quick check.
Arjay

9. One thing to be aware of is that the function you created is not generic and will depend on the type of equation and also the number of decimal points displayed.

Steve

10. The LINEST route is the only reliable one. See http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•