Chart

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Chart

Post by jimpatel1993 »

Thanks for looking at my post.

I have some data coming in automatically from A2 till C1000. Here in example i have mentioned only upto C16 and i have named as "Data1" for reference
I have added some column called Job1,2,3,4 and 5 in Row 19 and named as "Data2" for reference
I have 2 question please.
1. Is it possible to transfer data from "data1" to "Data2" as shown in the example please?
2. Secondly, is it possible to plot chart with respect to "Data2" please?
That is depending upon what is in the target in row 32 (currently 110 for this example) is it possible to do chart something like shown please?
That is, i dont want to exceed target with tolerance of +20% please?

Any idea pls
Thanks a lot
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart

Post by HansV »

Which version of Excel do you use?
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

Thanks for your reply Hans
Its 2016
Thanks
Will that make any difference? People might have different versions in there pc
Thanks

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart

Post by HansV »

Yes, it does make a difference. It means that we can't use the new functions available in Microsoft 365/Office 2021.

If you have a thousand rows, wouldn't the chart become extremely crowded?
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

I don't think I will get thousands of rows.
Max may be 2k rows
But it is most unlikely
Thanks

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart

Post by HansV »

See the attached version. It contains a macro to create the source range and the chart.

Chart.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

Thanks fantastic Hans
Thanks a lot for your time and effort.
Do you know how to do the tolerance range as well please from my original post.
That is we know target is 110 and I don't want to exceed any job after 110 with tolerance of plus 20%.
Any job after 20% tolerance of target wont be transfered to chart.

Secondly, is it something we can change
Instead of putting target in the vb code can we tie up with cell number?
Like what ever target in the actual cell.

Thanks a lot again
And once again you are the saviour

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart

Post by HansV »

Your data had a target for Job1 to Job5 only, but the chart had room for many more.
How many different jobs will there be?
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

There will be only 5 job in total but target will differ sometimes

Thanks a lot Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart

Post by HansV »

OK, that simplifies things (why did your original chart have more jobs?)

New version attached.

Chart.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

Hans
I must say you the king of macro.
Unbelievable work
Thanks a lot Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

Hi Hans,

Firstly thanks a lot for your solution. I have one small question for you on this code please.

In my actual workbook i am linking two sheets.
That is, in this example i attached, Sheet1 data is coming from Sheet2
But the issue is, i guess code does look for empty rows as well and throwing an error.
Any idea of how to fix please?
Thanks again
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart

Post by HansV »

It's easy to add a check:

Code: Select all

Sub CreateSource()
    Dim r As Long
    Dim m As Long
    Dim col As New Collection
    Dim itm
    Dim i As Long
    Dim c As Long
    Dim s As Long
    Dim ms As Long
    Dim cob As Shape
    Dim cht As Chart
    Dim ser As Series
    Dim tot(6 To 10) As Double
    Application.ScreenUpdating = False
    Range("F4:J" & Rows.Count).Clear
    On Error Resume Next
    ActiveSheet.ChartObjects(1).Delete
    On Error GoTo 0
    m = Cells(Rows.Count, 2).End(xlUp).Row
    For r = 2 To m
        itm = Cells(r, 2).Value
        If itm <> "" Then
            c = Range("F3:J3").Find(What:=itm, LookAt:=xlWhole).Column
            tot(c) = tot(c) + Cells(r, 3).Value
            If tot(c) <= Cells(1, c).Value * 1.2 Then
                s = Cells(Rows.Count, c).End(xlUp).Row + 1
                If s > ms Then ms = s
                Cells(s, c).Value = Cells(r, 3).Value
            End If
        End If
    Next r
    Set cob = ActiveSheet.Shapes.AddChart(Left:=288, Top:=144, Width:=576, Height:=432)
    Set cht = cob.Chart
    cht.ChartType = xlColumnStacked
    cht.SetSourceData Source:=Range("F3:J" & ms), PlotBy:=xlRows
    cht.HasLegend = False
    Set ser = cht.SeriesCollection.Add(Source:=Range("F1:J1"), RowCol:=xlRows)
    ser.ChartType = xlLine
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

:clapping:
That's fantastic.
I tried that but I guess I tried in wrong place.
Thanks a lot Hans for your prompt response again

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

Hi Hans,

Firstly thanks for your work. I need additional two favour with your experience pleaseee.
I have added sheet2 and i have button1. Is there some code you can point me for showing sheet1 in the userform pleasE?
That is when the "button1" is clicked and userform should reflect what is in sheet1 please. Further, can we modify sheet1 from the userform itself please?

Second favour: I have added some data in the sheet2. Is there any formula for doing this job please?
That is, this data is pulling from sheet1 which is highlighted in yellow and whenever it is modified can we pull the updated data please?

Any idea please?

Thanks a lot again :)
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart

Post by HansV »

See the attached version. Code for the userform is based on an example by Jon Peltier.

Chart with plus 20 percent criteria.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

:clapping: :clapping: :clapping:
Thanks a lot Hans
Much appreciated

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

Hi Hans,

Actually you helped me to solve big manual work and special thanks to you for that.
I got one more logic which is exactly similar to your previous one. I tried to change code but i did not get right solution.
Any help or pointing direction will be appreciated.
If not no issue Hans as you helped me a lot :)
I have attached 2 scenario.
Scenario 1: Depending upon target time, can we stack up job one by one?
If the target time is exceeding then person 2 will do the job and so on...

Scenario 2: If total job time for each number is sitting under target time then one person is enough.

and so on...

Finally,why can i not change Target line to red colour and it keep changing to blue colour please?

Thanks a lot Hans :)
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Chart

Post by HansV »

I'll skip your two scenarios and only answer your question about the line color: insert the following line above Application.ScreenUpdating = True:

Code: Select all

    ser.Format.Line.ForeColor.RGB = vbRed
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Chart

Post by jimpatel1993 »

Thanks a lot Hans
No problem.
I will try to find a way.
Thanks a lot for your reply Hans