PDA

View Full Version : Tracking changes of a cell value with a line chart



alinagoo
12-06-2013, 04:10 AM
Hi all
Please help me for the following problem.
My spread sheet has a cell with XML data source from a website. Cell value changes every one minute. Now I need to draw the chart of these changes. When the value of this cell changes, Excel must automatically draw the continues of a line chart to show me the trend of changes.
Thanks in advanced
A.M:think:

mancubus
12-06-2013, 06:18 AM
welcome to the forum.

Assumptions:

Web based data table is in a worksheet named "data".
A chart named "Chart 1" is already created in another worksheet named "chart".
Chart 1's data source is cell A2 and down in worksheet "chart".

Values in this range are copied from "F8" of worksheet "data" to the first blank cell below the existing data.



Try the Worksheet_Change Event code below. The code must be copied to "data" worksheet's code module. To do this, right click the tab name (data) then click "view code".


Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("chart")
.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = Worksheets("data").Range("F8").Value
.ChartObjects("Chart 1").Chart.SetSourceData .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
End Sub

----- PS: because of pasting problems regarding IE application in my office computer, the code is copied here as 1 line. in order to split this 1 line into real code lines, i added 'NEW_LINE string in the end. delete this string with the leading single quotation mark and hit ENTER key to rebuild lines. ----- PPS: uploading a file will help us help you.

Edited by SamT to add Paragraph breaks.

alinagoo
12-06-2013, 07:38 AM
Hello
Thanks :clap:
That works perfectly! Your solution to record a cell changes in a column is so interesting for me!

I have another question. Could you help me?
My problem is how to import data from a Non-HTML web page to an Excel worksheet. the web page shows many fields to the user but these fields are not in the form of HTML and they are not visible/unclosed so "Get external data" facilities of Excel are not able to read/extract the fields. is there any way to extracting data from these scripts-based web pages?

More thanks & Good Luck:cool:

mancubus
12-06-2013, 01:59 PM
you are welcome.

jolivanes
12-06-2013, 10:49 PM
@ mancubus
I don't know if it is the same problem but when I put code in a thread I had the same thing happening, all in one line.
I now copy the code into Notepad and from Notepad into the thread. That works for me.
If it is a different problem, sorry.
John

mancubus
12-07-2013, 04:58 AM
thanks John.

i informed the forum management on the problem.

it'se weird that i dont repeat the problem with my home computer.

another point is, i dont duplicate the problem with mrexcel, excelforum, etc that use the same platforms with my office computer. :huh:

i tried using notepad, notepad++ and write when i first faced with the problem. but they did not work for me.

so it's a mystery :)

jolivanes
12-07-2013, 09:28 AM
mancubus.
I certainly don't have another suggestion but I guess we can be confident that management will get it sussed.
Have a good weekend.
John

mancubus
12-07-2013, 03:26 PM
that must be something related with internet security protocols of the company i guess. a couple of times i got a "blocked" message when i tried to access the forum.

nevermind. i'll survive. :)

thanks again.