Replies

RetiredGeek
Thanks for the code. It took me awhile to determine the best way to implement it in my macro. Once done, it works like a charm. The main problem was that I had to abandon using cell references in the addchart stament if favor of using points. Once I did that it all came together.
Thanks Again
Arjay

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

Retiredgeek
Thanks for the additional suggestion. A nice elegant addition.
Arjay

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

I ended up having to Dim the variables as variant. i also forced the trendline through the origin. It works quite nicely. Thank you so much!
Sub chart()
‘
Dim cubiccoeff As Variant
Dim quadcoeff As Variant
Dim linearcoeff As Variant

‘name data ranges
Range(“F5”).Activate
Range(ActiveCell, Selection.End(xlDown)).Select
Selection.Name = “yPDCBData”
Range(“E5”).Activate
Range(ActiveCell, Selection.End(xlDown)).Select
Selection.Name = “xPDCBData”
Range(ActiveCell, ActiveCell.Offset(0, 1)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = “PDCBData”

‘calculate and print cubic regression coefficients
cubiccoeff = Evaluate(“=INDEX(LINEST(yPDCBData,xPDCBData^{1,2,3 },0,1),1)”)
quadcoeff = Evaluate(“=INDEX(LINEST(yPDCBData,xPDCBData^{1,2,3 },0,1),1,2)”)
linearcoeff = Evaluate(“=INDEX(LINEST(yPDCBData,xPDCBData^{1,2,3 },0,1),1,3)”)

Range(“AD32”).Value = “Trendline Coefficients”
Range(“AA34”).Value = “X^3”
Range(“AA35”).Value = “X^2”
Range(“AA36”).Value = “X”

Range(“AB33”).Value = “PDCB”
Range(“AB34”).Value = cubiccoeff
Range(“AB35”).Value = quadcoeff
Range(“AB36”).Value = linearcoeff

sdckapr
Interesting approach. As a newbie, I am still somewhat less than fluent in VBA. I understand what you did with the named ranges. It also occured to me as I was driving home that I may have Dim_ed vCubiccoeff incorrectly as well as having a “:” in the linest equation. I’ll give it a shot tomorrow morning when I get to work. But why use “Evaluate” instead of just “=” ?
Arjay13

Wow! I knew it was something goofy but had no clue as to what the issue was. I see that there is a listing of “Thanks”. How about Thank you, Thank you, Thank you! Three times in one post. I started programming back in the punch card days (and then stopped for 25 years) and I just knew that it something weird like that. I don’t get to SC very often (I was in Aiken two years back at the Savannah River Lab)but I do owe you a cold one! Have a great weekend.

27985-GC2-R10-NA6-Standards-Mar-25-2011
RG
Interestingly, when I started with a new spreadsheet and manually entered the headers, the macro worked like a charm. However, it doesnt work on my data files for no discernible reason to me. Other macros have worked just fine so I am stumped. Have a look and see what you think. Thanks!

Thanks sdckapr! That works perfectly. I am new to VBA and don’t have an extensive knowledge of the syntax yet. I never would have come up with that solution. I will study it so I can get a little bit better. Thanks Again!
Arjay

I’d like the macro to continue as if nothing happened. In other words, if I click on “no” or “cancel” I don’t want the code to do anything- just proceed as if nothing happened.

This is a portion of my macro. The runtime error is generated on the last line when there is an existing file with the same name I am using to save the current workbook.

Private Sub Ok_Click()
Dim Workrange As Range
Dim Oldsheet As String
Dim newsheet As String
Dim oldworkbook As String
Dim Saveworkbook As String

‘macro to save CSV as an xls & add a sheet & return to the original sheet

oldworkbook = ActiveWorkbook.Name
‘replace csv with xls as file extension
Saveworkbook = Replace(oldworkbook, “csv”, “xls”)
‘save file as xls
ActiveWorkbook.SaveAs Filename:=Saveworkbook, FileFormat:=xlExcel8

Sorry – I get:
Run-time error ‘1004’:
Method ‘Save as’ of ‘object_workbook’ failed

geof
The only reason stopping me is I am learning a new programming language – so i start with simple steps. In this case, S and s would signify the same thing to me, so they actually sould be in the same range or region. I will try again on Monday. Thank you for your insight. Btw, I was in Aukland 10 years ago. Wonderful place. Got down to Rota Rua to the geothermal springs. Didn’t get down to south island though. Have a college buddy down in Christchurch. He luckily missed the earthquake by a couple of miles. thanks for your input and be well.
Arjay13

sdckapr
as a newbie to VBA programming, I did not fully appreciate all the nuances of the code. I just started reading books last week. Thank you!

Ah Yes. In the back of my tiny mind, I was thinking about an Else…nicely done