Previous Topic Next Topic

VB, How to pull formula on graph to a cell?

Closed
weenie - Oct 12, 2011 at 05:45 PM
 weenie - Dec 21, 2011 at 01:34 PM
Hello,

I already have a VB in place that brings in data and from that data I have a trendline, exponential, and displaying the formula on the graph. Obviously, this formula changes depending on the data brought in. How do I write in VB to pull the formula from the graph and into a cell on the same worksheet "Chart C"? I tried recording macro of grabbing the formula and pasting into a cell (i.e. C35) but when I tried it out it just copied the entire graph. I don't want a copy of the graph just the formula it generated. Any help would be greatly appreciated.

Thanks,
weenie

Related:

3 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 13, 2011 at 09:43 AM
Hi Weenie,

Not sure what you mean by "displaying the formula on the graph".
Could you post (a sample of) your workbook using a filesharing site like www.speedyshare.com to see if I can help you?. I'm still using Excel 2003 so make sure the file extention is .xls.

Best regards,
Trowa
weenie
Dec 16, 2011 at 05:19 PM
Sorry I missed this reply but looks like I have been given a different direction so this is scrapped above. I have an entirley different problem with the GROWTH formula in excel. I can not seem to enter a range in VB that allows dynamic range. This function only has a static range entry.
=GROWTH(B3:B10,A3:A10,D14,TRUE)
The range will always start at row 3 but will be vary for last row (not always be row 10). Is there a way to write in VB to start at row 3 to last cell with a value for GROWTH function?
Thanks,
Weenie
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Dec 19, 2011 at 09:47 AM
Hi Weenie,

A great way to make static range dynamic is to use named ranges.
Right click on the sheets tab and choose view code.
Paste the following code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow, lRow2 As Integer
lRow = Cells(Rows.Count, 1).End(xlUp).Row
lRow2 = Cells(Rows.Count, 2).End(xlUp).Row
Range("A3:A" & lRow).Name = "RangeA"
Range("B3:B" & lRow2).Name = "RangeB"
End Sub

Whenever you make a change in columns A and B of the sheet you are using, the used range in column A and B will be updated.
Now you need to use these named ranges in your formula:
=GROWTH(RangeB,RangeA,D14,TRUE)

NOTE: Make sure to make a change in column A and B for the code to apply the named ranges.
Also make sure that both ranges have the same size for the function to work.

Best regards,
Trowa
weenie
Dec 21, 2011 at 01:34 PM
WOW, It worked and I never knew you could put code on tab sheet by doing the view code instructions above. Always learning something new..cool.
Thanks a bunch,
Weenie
Answer 2 / 3
weenie
Oct 14, 2011 at 02:25 PM
here is the link from speedyshare

http://www.speedyshare.com/files/30748234/Booktofigureout.xlsx

I have a trendline, exponential to estimate Lifetime and I selected to "display the equation on chart". I can't seem to figure out how to copy the equation to a cell.
Answer 3 / 3
weenie
Oct 14, 2011 at 02:35 PM
http://www.speedyshare.com/files/30748460/Booktofigureout.xls

Since you have 2003
hope it works
Thanks
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 17, 2011 at 08:53 AM
Hi Weenie,

The uploaded workbook only contains one sheet, named "Booktofigureout" which is completely empty.

Could you check the workbook and try again?

Best regards,
Trowa

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response