Dual Line Graph

rcb007

New Member
Joined
Nov 12, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
I am trying to figure out how to create a macro that can plot 4 points from 4 different cells, with a possible horizontal line connecting the points.

Example.
A1=100
A2=90
A3=103
A4=85

(A1)._______________.(A3)


(A2)._______________.(A4)


With that, I have several rows that have numbers like the above. How could I select a row and it plots the graph, if possible?

Thank you for any help!!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Not clear on what you want to achieve.
I have several rows that have numbers like the above
do you mean several colums like the above (as A1:A4 is in one column)?


to get a graph like
1605620162249.png

you will need to split up each column in two columns

So that your
A1=100
A2=90
A3=103
A4=85

becomes
A1=100
A2=90
B1=103
B2=85
1605620288840.png


You could do that with a macro.

Does that help?
 

rcb007

New Member
Joined
Nov 12, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
I think you are definitely on the right track. I have attached a screen shot of (hopefully) a better diagram of showing what I would like to do wit the graph.

The Graph you showed above is exactly what I am wanting to do.

I would want to select (example pipe 1) the 4 cells (highlighted) and then be able to see the numbers in the graph form. If I select the pipe 3 line with the 4 cells, I would like to see those numbers on the graph.

I hope this makes it more clear.
 

Attachments

  • Capture.JPG
    Capture.JPG
    48.3 KB · Views: 2

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Here is what I have made
1605714573173.png


If the user selects a cell somewhere in the range of pipe coordinates, the graph will be modified for the row. So select AB22 and Pipe3 coordinates will be shown. Select AD20 and Pipe1 will be shown.

To make this work, you first need to create your graph. (Do this before you have copied the macros, else you will get endless error messages)
The easy way (showing incorrect lines, but that doesn't matter at this stage) is to select AC20:AD21. Then insert 2D line graph.

If you already have a graph, then ignore the above.

Note the name of your graph.

Paste the following code into a normal VBA module (press Alt-F8, then type in xx and Create... button. This will open the VBA editor. Delete the macro created, paste the below in instead)
VBA Code:
Option Explicit

Sub ModifyGraphSource(wsWS As Worksheet, rR As Range)
' Adjust the series in a 2D line chart to the values
' given in the row provided by rR
' Also adjust the title of the graph

    Dim chChrt As ChartObject
    Dim rIn As Range, rStr1 As Range, rStr2 As Range
    
    If rR.Cells.Count > 1 Then Exit Sub 'Ignore if user selected several cells
    
    On Error GoTo NoChart
    Set chChrt = wsWS.ChartObjects("Chart 2") '<<<< Modify chartname as required
    On Error GoTo 0
    
    'from the selected cell, get the input row and the two ranges for the lines
    Set rIn = rR.CurrentRegion
    Set rIn = Range(Cells(rR.Row, rIn.Column), Cells(rR.Row, rIn.Column + rIn.Columns.Count - 1))
    Set rStr1 = rIn.Cells(1, 2).Resize(1, 2)
    Set rStr2 = rIn.Cells(1, 4).Resize(1, 2)

    'now update the chart
    With chChrt.Chart
        .SeriesCollection(1).Values = wsWS.Name & "!" & rStr1.Address
        .SeriesCollection(2).Values = wsWS.Name & "!" & rStr2.Address
        .ChartTitle.Caption = rIn.Cells(1, 1).Value
    End With
    Exit Sub
    
NoChart:
    MsgBox "The chart has not been found.", Buttons:=vbCritical + vbOKOnly
    On Error GoTo 0
End Sub

Notice in the comments (green) the comment starting with <<<<. Modify the chart name as to the one you have

Now go back to Excel and right click on the nametab of the sheet. Select view code...
This will open the code for this sheet. Insert the following code here:
VBA Code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const sAddress As String = "AB21"   '<<<< Left top corner of range with Pipe coordinates
    Dim rIn As Range
    
    'get the full range of all the piping coordinates
    Set rIn = Range(sAddress).CurrentRegion
    
    'check to see if the selected cell(s) is in this area
    If Not Intersect(Target, rIn) Is Nothing Then
        'selected cell is in piping coordinates area
        'so modify the graph
        ModifyGraphSource Me, Target
    End If
    
End Sub

That's it. Go back to Excel and select a cell in the range to see the graph being modified
 

Watch MrExcel Video

Forum statistics

Threads
1,122,975
Messages
5,599,129
Members
414,292
Latest member
kingshuk963

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