charts with 2 changing cells

[Good Guy]

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

 

[Worf]

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]

There are no formula in the cell.

i get time in the A cell with

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]

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:

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]

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]

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

 

[Good Guy]

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]

Hi Good Guy

See if this example is useful to you; first, create a blank chart, using an empty range as data source:

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:

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]

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]

OK then, problem solved...

Thanks for the feedback.