charts with 2 changing cells

Good Guy

New Member
Joined
Nov 7, 2011
Messages
7
Hello guys i urgently need your help.

I have 2 cells in excel. First is automatic time(i get this time after second cell is changed), and the second cell is number which i get from external controller. i get this value every minute for whole day.

I need chart from this 2 values through whole day(every minute is point on the graph).

I was checking the internet whole day but i didn't find the solution for this.

Thanks!

EDIT: i am using excel 2010
 
Last edited:

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
Hello guys i urgently need your help.

I have 2 cells in excel. First is automatic time(i get this time after second cell is changed), and the second cell is number which i get from external controller. i get this value every minute for whole day.
I need chart from this 2 values through whole day(every minute is point on the graph).
EDIT: i am using excel 2010

Can you explain further? What are the formulas in the cells, if any?
Is there VBA code involved?
In other words, how exactly the values change?
 

Good Guy

New Member
Joined
Nov 7, 2011
Messages
7
There are no formula in the cell.

i get time in the A cell with
Code:
If Target.Value <> "" Then
        Cells(Target.Row, 1).Value = Time
        End If

and there is vba for saving sheet to pdf after excel is closed(end of the day).

This is all the vba code so far.

Values which it will go in B cell i will get from external chip which is connected to the some machine(this part is covered by friend). He has only told me that this chip can provide data to one cell only, which will change every minute.

What kind of vba code if any is behind that process i don't know yet, but i will get that info tomorrow if it's important for you?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
I don' t think the chip details are relevant to this problem.
Do you have the complete code for the VBA macro you posted?

Maybe the solution can use the following technique:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    MsgBox "You just changed " & Target.Address

End Sub

Every time the numbers change, the values are written to a range of cells. Afterwards, the chart is drawn from this data.
 

Good Guy

New Member
Joined
Nov 7, 2011
Messages
7

ADVERTISEMENT

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrExit
If Target.Value <> "" Then
        Cells(Target.Row, 1).Value = Time
        End If

'or this 
'If Not IsEmpty(Cells(Target.Row, "A")) Then Exit Sub
  ' If Not IsEmpty(Target.Value) Then Cells(Target.Row, "A") = Time
   
    Dim oChrt As ChartObject
    Dim szSeries As String
    
    Set oChrt = ActiveSheet.ChartObjects(1)
    
    If Not oChrt Is Nothing Then
    
    For Each oChrt In ChartObjects
    
      
        szSeries = oChrt.Chart.SeriesCollection(1).Formula
        
        szSeries = Left(szSeries, InStrRev(szSeries, ",") - 1)
        
        szSeries = Right(szSeries, (Len(szSeries) - InStrRev(szSeries, ",")))
        
        oChrt.Chart.SetSourceData Source:=Range(szSeries).CurrentRegion
        
        
    Next oChrt
        
    End If
    
    Set oChrt = Nothing
ErrExit:
  
  End Sub

here you go.

So you suggest that i get for every new value new row(A time and B value) and then i draw dynamic chart with code above?

If this is so, how should i move on value change cell lover, with no human interaction?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
Do you want the chart real time, or can it be generated at the end of the day?
 

Good Guy

New Member
Joined
Nov 7, 2011
Messages
7

ADVERTISEMENT

Do you want the chart real time, or can it be generated at the end of the day?

It would be great if this was possible in real time, because, manager of that machine will be checking that graph let's say 5 times at day.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
Hi Good Guy

See if this example is useful to you; first, create a blank chart, using an empty range as data source:
Code:
Sub BlankChart()
    Dim sc As Series, ch As ChartObject
    Set ch = Worksheets("Process").ChartObjects.Add(100, 30, 400, 250)
    ch.Chart.SeriesCollection.NewSeries
    Set sc = ch.Chart.SeriesCollection(1)
    sc.ChartType = xlLine
    sc.XValues = "='Process'!$D$11:$D$20"
    sc.Values = "='Process'!$E$11:$E$20"
End Sub

Then, use the following code to write to that range, whenever the process sends new data to cell B2:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lrow&
If Target.Address = "$B$2" And Not IsEmpty(Target.Value) Then
    lrow = LastRow + 1
    cells(lrow, 4).Value = Time
    cells(lrow, 5).Value = cells(2, 2).Value
End If
End Sub

Public Function LastRow() As Long
    If WorksheetFunction.CountA(cells) = 0 Then
        LastRow = 0
        Exit Function
    End If
    LastRow = cells.Find(what:="*", after:=[a1], searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
End Function

The chart will update automatically. You can adapt this to your specific needs.
 

Good Guy

New Member
Joined
Nov 7, 2011
Messages
7
Tnx for you help, but i have solved this with OnTime event

since values are changing every minute my timer will copy values to new rows every 55 sec and from that i will get graph.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
OK then, problem solved... :)

Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top