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

3 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
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
0
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.
0
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0

Similar discussions

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

6 responses
Formula to subtract one cell value from another.
autigers71 -
vcoolio -

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

1 response
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses