Chart
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Chart
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
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.
-
- Administrator
- Posts: 78416
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Chart
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?
If you have a thousand rows, wouldn't the chart become extremely crowded?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78416
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Chart
See the attached version. It contains a macro to create the source range and the chart.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Chart
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
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
-
- Administrator
- Posts: 78416
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Chart
OK, that simplifies things (why did your original chart have more jobs?)
New version attached.
New version attached.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Chart
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
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.
-
- Administrator
- Posts: 78416
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Chart
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
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Chart
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 :)
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.
-
- Administrator
- Posts: 78416
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Chart
See the attached version. Code for the userform is based on an example by Jon Peltier.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Chart
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 :)
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.
-
- Administrator
- Posts: 78416
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Chart
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
Hans