OK, I have an interesting issue. For the most part my macro seems to
work for creating and updating charts dynamically. But I am running
into an issue. If the chart data gets too large then it drops some of
the selections and errors out.

The following is a string in my macro for the series collection:

X = "=Employee_Hours!R652C1,Employee_Hours!R655C1,Empl oyee_Hours!
R660C1,Employee_Hours!R667C1,Employee_Hours!R670C1 ,Employee_Hours!
R673C1,Employee_Hours!R676C1,Employee_Hours!R679C1 ,Employee_Hours!
R682C1,Employee_Hours!R685C1,Employee_Hours!R688C1 "

Y = "=Employee_Hours!R653C4,Employee_Hours!R656C4,Empl oyee_Hours!
R661C4,Employee_Hours!R668C4,Employee_Hours!R671C4 ,Employee_Hours!
R674C4,Employee_Hours!R677C4,Employee_Hours!R680C4 ,Employee_Hours!
R683C4,Employee_Hours!R686C4,Employee_Hours!R689C4 "

This shows correctly when I debug it, but I get an error. If I resume
next and ignore the error and view the chart source I get the
following:

Y = =Employee_Hours!$D$653,Employee_Hours!$D$656,Emplo yee_Hours!$D
$661,Employee_Hours!$D$668,Employee_Hours!$D$671,E mployee_Hours!$D
$674,Employee_Hours!$D$677,Employee_Hours!$D$680,E mployee_Hours!$D
$683,Employee_Hours!$D$686 Employee_Hours!

X = =Employee_Hours!$A$652,Employee_Hours!$A$655,Emplo yee_Hours!$A
$660,Employee_Hours!$A$667,Employee_Hours!$A$670,E mployee_Hours!$A
$673,Employee_Hours!$A$676,Employee_Hours!$A$679,E mployee_Hours!$A
$682,Employee_Hours!$A$685 Employee_Hours!

The issue for some odd reason or another is the last part of these
entries. They are missing part of the string (It is missing the comma
and the last selection).

It does not happen on all of the employee data when I select them. I
am not sure what is happening being that if I modify the chart
selection and fix it manually it will display correctly

Can someone help me understand why this is happening

Have you tested to see if it is a string length issue? E.g., if you rename
your sheet Empl_Hrs and shorten your strings below, does that fix the
problem?

Keith

however, your code ends with
wrote in message
...
OK, I have an interesting issue. For the most part my macro seems to
work for creating and updating charts dynamically. But I am running
into an issue. If the chart data gets too large then it drops some of
the selections and errors out.

The following is a string in my macro for the series collection:

X = "=Employee_Hours!R652C1,Employee_Hours!R655C1,Empl oyee_Hours!
R660C1,Employee_Hours!R667C1,Employee_Hours!R670C1 ,Employee_Hours!
R673C1,Employee_Hours!R676C1,Employee_Hours!R679C1 ,Employee_Hours!
R682C1,Employee_Hours!R685C1,Employee_Hours!R688C1 "

Y = "=Employee_Hours!R653C4,Employee_Hours!R656C4,Empl oyee_Hours!
R661C4,Employee_Hours!R668C4,Employee_Hours!R671C4 ,Employee_Hours!
R674C4,Employee_Hours!R677C4,Employee_Hours!R680C4 ,Employee_Hours!
R683C4,Employee_Hours!R686C4,Employee_Hours!R689C4 "

This shows correctly when I debug it, but I get an error. If I resume
next and ignore the error and view the chart source I get the
following:

Y = =Employee_Hours!$D$653,Employee_Hours!$D$656,Emplo yee_Hours!$D
$661,Employee_Hours!$D$668,Employee_Hours!$D$671,E mployee_Hours!$D
$674,Employee_Hours!$D$677,Employee_Hours!$D$680,E mployee_Hours!$D
$683,Employee_Hours!$D$686 Employee_Hours!

X = =Employee_Hours!$A$652,Employee_Hours!$A$655,Emplo yee_Hours!$A
$660,Employee_Hours!$A$667,Employee_Hours!$A$670,E mployee_Hours!$A
$673,Employee_Hours!$A$676,Employee_Hours!$A$679,E mployee_Hours!$A
$682,Employee_Hours!$A$685 Employee_Hours!

The issue for some odd reason or another is the last part of these
entries. They are missing part of the string (It is missing the comma
and the last selection).

It does not happen on all of the employee data when I select them. I
am not sure what is happening being that if I modify the chart
selection and fix it manually it will display correctly

Can someone help me understand why this is happening

On Jan 21, 11:55*am, "Keith R" wrote:
Have you tested to see if it is a string length issue? E.g., if you rename
your sheet Empl_Hrs and shorten your strings below, does that fix the
problem?

Keith

however, your code ends wrote in message

...



OK, I have *an interesting issue. *For the most part my macro seems to
work for creating and updating charts dynamically. *But I am running
into an issue. *If the chart data gets too large then it drops some of
the selections and errors out.

The following is a string in my macro for the series collection:

X = "=Employee_Hours!R652C1,Employee_Hours!R655C1,Empl oyee_Hours!
R660C1,Employee_Hours!R667C1,Employee_Hours!R670C1 ,Employee_Hours!
R673C1,Employee_Hours!R676C1,Employee_Hours!R679C1 ,Employee_Hours!
R682C1,Employee_Hours!R685C1,Employee_Hours!R688C1 "

Y = "=Employee_Hours!R653C4,Employee_Hours!R656C4,Empl oyee_Hours!
R661C4,Employee_Hours!R668C4,Employee_Hours!R671C4 ,Employee_Hours!
R674C4,Employee_Hours!R677C4,Employee_Hours!R680C4 ,Employee_Hours!
R683C4,Employee_Hours!R686C4,Employee_Hours!R689C4 "

This shows correctly when I debug it, but I get an error. *If I resume
next and ignore the error and view the chart source I get the
following:

Y = =Employee_Hours!$D$653,Employee_Hours!$D$656,Emplo yee_Hours!$D
$661,Employee_Hours!$D$668,Employee_Hours!$D$671,E mployee_Hours!$D
$674,Employee_Hours!$D$677,Employee_Hours!$D$680,E mployee_Hours!$D
$683,Employee_Hours!$D$686 Employee_Hours!

X = =Employee_Hours!$A$652,Employee_Hours!$A$655,Emplo yee_Hours!$A
$660,Employee_Hours!$A$667,Employee_Hours!$A$670,E mployee_Hours!$A
$673,Employee_Hours!$A$676,Employee_Hours!$A$679,E mployee_Hours!$A
$682,Employee_Hours!$A$685 Employee_Hours!

The issue for some odd reason or another is the last part of these
entries. *They are missing part of the string (It is missing the comma
and the last selection).

It does not happen on all of the employee data when I select them. *I
am not sure what is happening being that if I modify the chart
selection and fix it manually it will display correctly

Can someone help me understand why this is happening- Hide quoted text -

- Show quoted text -

I just tried that, and that seems to be my issue. How can have my
macro split the vairable into something less and then join it into one
large string at the end

Here is my current code:

Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long
Dim cName As String
Dim scName As String
Dim rgA1 As String
Dim rgA2 As String
Dim rgD1 As String
Dim rgD2 As String
Dim rngTotal As String
Dim cDataX As String
Dim cDataY As String
Application.ScreenUpdating = False
rgA1 = ""
rgD1 = ""
rgA2 = ""
rgD2 = ""
rngTotal = ""
cDataX = ""
cDataY = ""
Application.Worksheets("Employee_Charts").Activate

cName = Range("B3").Value
Sheets("Empl_Hrs").Select
Range("A2").Select

Do While ActiveCell.Value < "" Or _
ActiveCell.Offset(0, 1).Value < ""
scName = ActiveCell.Value
If UCase(scName) = UCase(cName) Then
ActiveCell.Offset(1, 0).Select
rgA1 = ActiveCell.Row
rgD1 = ActiveCell.Row
Do While ActiveCell.Offset(0, 1).Value < ""
If ActiveCell.Offset(0, 1).Value = "Total Hours" Then
If Not cDataX < "" Then
cDataX = "A" & ActiveCell.Offset(-1, 0).Row
Else
cDataX = cDataX & "," & "A" & ActiveCell.Offset(-1, 0).Row
End If
If Not cDataY < "" Then
cDataY = "D" & ActiveCell.Row
Else
cDataY = cDataY & "," & "D" & ActiveCell.Row
End If
End If
If ActiveCell.Offset(0, 1).Value = "Total Working Hours" Then
rgA2 = ActiveCell.Offset(-2, 0).Row
rgD2 = ActiveCell.Offset(-2, 0).Row
End If
ActiveCell.Offset(1, 0).Select
Loop
End If
ActiveCell.Offset(1, 0).Select
Loop
If Not cDataX < "" Then
Sheets("Employee_Charts").Select
MsgBox "No Data Found." & Chr(10) & "Have a Nice Day!",
vbInformation

Exit Sub
End If
rgA1 = "A" & rgA1 & ":" & "A" & rgA2
rgD1 = "C" & rgD1 & ":" & "C" & rgD2
rngTotal = rgA1 & "," & rgD1
Sheets("Employee_Charts").Select
Set wsData = Worksheets("Empl_Hrs")
Set rDataX = wsData.Range(cDataX)
Set rDataY = wsData.Range(cDataY)


For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & wsData.Name & "!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)


For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & wsData.Name & "!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)




ActiveSheet.ChartObjects.Select
ActiveChart.ChartTitle.Text = cName & Chr(10) & "Working Hours
Summary Report"
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = sAddressX
ActiveChart.SeriesCollection(1).Values = sAddressY
On Error Resume Next
ActiveChart.SeriesCollection(1).Name = "=Empl_Hrs!R1C3"
Dim Thomas As String
Thomas = "hi"

Range("B3").Select
Application.ScreenUpdating = True

Post on top, so it's easier to read the thread in sequence.

How can have my macro split the vairable
into something less and then join it into one
large string at the end

If the issue is string length, why would forming a large string at the end
be better than at the beginning?

You should strive to use contiguous ranges as your chart's source data.
Sometimes this means setting up a summary range that the chart uses.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
...
On Jan 21, 11:55 am, "Keith R" wrote:
Have you tested to see if it is a string length issue? E.g., if you rename
your sheet Empl_Hrs and shorten your strings below, does that fix the
problem?

Keith

however, your code ends wrote in message

...



OK, I have an interesting issue. For the most part my macro seems to
work for creating and updating charts dynamically. But I am running
into an issue. If the chart data gets too large then it drops some of
the selections and errors out.

The following is a string in my macro for the series collection:

X = "=Employee_Hours!R652C1,Employee_Hours!R655C1,Empl oyee_Hours!
R660C1,Employee_Hours!R667C1,Employee_Hours!R670C1 ,Employee_Hours!
R673C1,Employee_Hours!R676C1,Employee_Hours!R679C1 ,Employee_Hours!
R682C1,Employee_Hours!R685C1,Employee_Hours!R688C1 "

Y = "=Employee_Hours!R653C4,Employee_Hours!R656C4,Empl oyee_Hours!
R661C4,Employee_Hours!R668C4,Employee_Hours!R671C4 ,Employee_Hours!
R674C4,Employee_Hours!R677C4,Employee_Hours!R680C4 ,Employee_Hours!
R683C4,Employee_Hours!R686C4,Employee_Hours!R689C4 "

This shows correctly when I debug it, but I get an error. If I resume
next and ignore the error and view the chart source I get the
following:

Y = =Employee_Hours!$D$653,Employee_Hours!$D$656,Emplo yee_Hours!$D
$661,Employee_Hours!$D$668,Employee_Hours!$D$671,E mployee_Hours!$D
$674,Employee_Hours!$D$677,Employee_Hours!$D$680,E mployee_Hours!$D
$683,Employee_Hours!$D$686 Employee_Hours!

X = =Employee_Hours!$A$652,Employee_Hours!$A$655,Emplo yee_Hours!$A
$660,Employee_Hours!$A$667,Employee_Hours!$A$670,E mployee_Hours!$A
$673,Employee_Hours!$A$676,Employee_Hours!$A$679,E mployee_Hours!$A
$682,Employee_Hours!$A$685 Employee_Hours!

The issue for some odd reason or another is the last part of these
entries. They are missing part of the string (It is missing the comma
and the last selection).

It does not happen on all of the employee data when I select them. I
am not sure what is happening being that if I modify the chart
selection and fix it manually it will display correctly

Can someone help me understand why this is happening- Hide quoted text -

- Show quoted text -

I just tried that, and that seems to be my issue. How can have my
macro split the vairable into something less and then join it into one
large string at the end

Here is my current code:

Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long
Dim cName As String
Dim scName As String
Dim rgA1 As String
Dim rgA2 As String
Dim rgD1 As String
Dim rgD2 As String
Dim rngTotal As String
Dim cDataX As String
Dim cDataY As String
Application.ScreenUpdating = False
rgA1 = ""
rgD1 = ""
rgA2 = ""
rgD2 = ""
rngTotal = ""
cDataX = ""
cDataY = ""
Application.Worksheets("Employee_Charts").Activate

cName = Range("B3").Value
Sheets("Empl_Hrs").Select
Range("A2").Select

Do While ActiveCell.Value < "" Or _
ActiveCell.Offset(0, 1).Value < ""
scName = ActiveCell.Value
If UCase(scName) = UCase(cName) Then
ActiveCell.Offset(1, 0).Select
rgA1 = ActiveCell.Row
rgD1 = ActiveCell.Row
Do While ActiveCell.Offset(0, 1).Value < ""
If ActiveCell.Offset(0, 1).Value = "Total Hours" Then
If Not cDataX < "" Then
cDataX = "A" & ActiveCell.Offset(-1, 0).Row
Else
cDataX = cDataX & "," & "A" & ActiveCell.Offset(-1, 0).Row
End If
If Not cDataY < "" Then
cDataY = "D" & ActiveCell.Row
Else
cDataY = cDataY & "," & "D" & ActiveCell.Row
End If
End If
If ActiveCell.Offset(0, 1).Value = "Total Working Hours" Then
rgA2 = ActiveCell.Offset(-2, 0).Row
rgD2 = ActiveCell.Offset(-2, 0).Row
End If
ActiveCell.Offset(1, 0).Select
Loop
End If
ActiveCell.Offset(1, 0).Select
Loop
If Not cDataX < "" Then
Sheets("Employee_Charts").Select
MsgBox "No Data Found." & Chr(10) & "Have a Nice Day!",
vbInformation

Exit Sub
End If
rgA1 = "A" & rgA1 & ":" & "A" & rgA2
rgD1 = "C" & rgD1 & ":" & "C" & rgD2
rngTotal = rgA1 & "," & rgD1
Sheets("Employee_Charts").Select
Set wsData = Worksheets("Empl_Hrs")
Set rDataX = wsData.Range(cDataX)
Set rDataY = wsData.Range(cDataY)


For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & wsData.Name & "!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)


For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & wsData.Name & "!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)




ActiveSheet.ChartObjects.Select
ActiveChart.ChartTitle.Text = cName & Chr(10) & "Working Hours
Summary Report"
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = sAddressX
ActiveChart.SeriesCollection(1).Values = sAddressY
On Error Resume Next
ActiveChart.SeriesCollection(1).Name = "=Empl_Hrs!R1C3"
Dim Thomas As String
Thomas = "hi"

Range("B3").Select
Application.ScreenUpdating = True

On Jan 21, 1:01*pm, "Jon Peltier"
wrote:
Post on top, so it's easier to read the thread in sequence.

How can have my macro split the vairable
into something less and then join it into one
large string at the end

If the issue is string length, why would forming a large string at the end
be better than at the beginning?

You should strive to use contiguous ranges as your chart's source data.
Sometimes this means setting up a summary range that the chart uses.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...
On Jan 21, 11:55 am, "Keith R" wrote:





Have you tested to see if it is a string length issue? E.g., if you rename
your sheet Empl_Hrs and shorten your strings below, does that fix the
problem?

Keith

however, your code ends wrote in message

...

OK, I have an interesting issue. For the most part my macro seems to
work for creating and updating charts dynamically. But I am running
into an issue. If the chart data gets too large then it drops some of
the selections and errors out.

The following is a string in my macro for the series collection:

X = "=Employee_Hours!R652C1,Employee_Hours!R655C1,Empl oyee_Hours!
R660C1,Employee_Hours!R667C1,Employee_Hours!R670C1 ,Employee_Hours!
R673C1,Employee_Hours!R676C1,Employee_Hours!R679C1 ,Employee_Hours!
R682C1,Employee_Hours!R685C1,Employee_Hours!R688C1 "

Y = "=Employee_Hours!R653C4,Employee_Hours!R656C4,Empl oyee_Hours!
R661C4,Employee_Hours!R668C4,Employee_Hours!R671C4 ,Employee_Hours!
R674C4,Employee_Hours!R677C4,Employee_Hours!R680C4 ,Employee_Hours!
R683C4,Employee_Hours!R686C4,Employee_Hours!R689C4 "

This shows correctly when I debug it, but I get an error. If I resume
next and ignore the error and view the chart source I get the
following:

Y = =Employee_Hours!$D$653,Employee_Hours!$D$656,Emplo yee_Hours!$D
$661,Employee_Hours!$D$668,Employee_Hours!$D$671,E mployee_Hours!$D
$674,Employee_Hours!$D$677,Employee_Hours!$D$680,E mployee_Hours!$D
$683,Employee_Hours!$D$686 Employee_Hours!

X = =Employee_Hours!$A$652,Employee_Hours!$A$655,Emplo yee_Hours!$A
$660,Employee_Hours!$A$667,Employee_Hours!$A$670,E mployee_Hours!$A
$673,Employee_Hours!$A$676,Employee_Hours!$A$679,E mployee_Hours!$A
$682,Employee_Hours!$A$685 Employee_Hours!

The issue for some odd reason or another is the last part of these
entries. They are missing part of the string (It is missing the comma
and the last selection).

It does not happen on all of the employee data when I select them. I
am not sure what is happening being that if I modify the chart
selection and fix it manually it will display correctly

Can someone help me understand why this is happening- Hide quoted text -

- Show quoted text -

I just tried that, and that seems to be my issue. *How can have my
macro split the vairable into something less and then join it into one
large string at the end

Here is my current code:

Dim rDataX As Range
* Dim rDataY As Range
* Dim wsData As Worksheet
* Dim sAddressX As String
* Dim sAddressY As String
* Dim iArea As Long
* Dim cName As String
* Dim scName As String
* Dim rgA1 As String
* Dim rgA2 As String
* Dim rgD1 As String
* Dim rgD2 As String
* Dim rngTotal As String
* Dim cDataX As String
* Dim cDataY As String
* Application.ScreenUpdating = False
* rgA1 = ""
* rgD1 = ""
* rgA2 = ""
* rgD2 = ""
* rngTotal = ""
* cDataX = ""
* cDataY = ""
* Application.Worksheets("Employee_Charts").Activate

* cName = Range("B3").Value
* Sheets("Empl_Hrs").Select
* Range("A2").Select

* Do While ActiveCell.Value < "" Or _
* * ActiveCell.Offset(0, 1).Value < ""
* *scName = ActiveCell.Value
* *If UCase(scName) = UCase(cName) Then
* * ActiveCell.Offset(1, 0).Select
* * rgA1 = ActiveCell.Row
* * rgD1 = ActiveCell.Row
* * Do While ActiveCell.Offset(0, 1).Value < ""
* * *If ActiveCell.Offset(0, 1).Value = "Total Hours" Then
* * * *If Not cDataX < "" Then
* * * * cDataX = "A" & ActiveCell.Offset(-1, 0).Row
* * * *Else
* * * * cDataX = cDataX & "," & "A" & ActiveCell.Offset(-1, 0).Row
* * * *End If
* * * *If Not cDataY < "" Then
* * * * cDataY = "D" & ActiveCell.Row
* * * *Else
* * * * cDataY = cDataY & "," & "D" & ActiveCell.Row
* * * *End If
* * *End If
* * * If ActiveCell.Offset(0, 1).Value = "Total Working Hours" Then
* * * * rgA2 = ActiveCell.Offset(-2, 0).Row
* * * * rgD2 = ActiveCell.Offset(-2, 0).Row
* * * End If
* * ActiveCell.Offset(1, 0).Select
* * Loop
* *End If
* ActiveCell.Offset(1, 0).Select
* Loop
* If Not cDataX < "" Then
* *Sheets("Employee_Charts").Select
* *MsgBox "No Data Found." & Chr(10) & "Have a Nice Day!",
vbInformation

* *Exit Sub
* End If
*rgA1 = "A" & rgA1 & ":" & "A" & rgA2
*rgD1 = "C" & rgD1 & ":" & "C" & rgD2
*rngTotal = rgA1 & "," & rgD1
*Sheets("Employee_Charts").Select
*Set wsData = Worksheets("Empl_Hrs")
*Set rDataX = wsData.Range(cDataX)
*Set rDataY = wsData.Range(cDataY)

* For iArea = 1 To rDataX.Areas.Count
* * sAddressX = sAddressX & wsData.Name & "!"
* * sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
* Next
* sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

* For iArea = 1 To rDataY.Areas.Count
* * sAddressY = sAddressY & wsData.Name & "!"
* * sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
* Next
* sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

*ActiveSheet.ChartObjects.Select
*ActiveChart.ChartTitle.Text = cName & Chr(10) & "Working Hours
Summary Report"
*ActiveChart.SeriesCollection(1).Delete
*ActiveChart.SeriesCollection.NewSeries
*ActiveChart.SeriesCollection(1).XValues = sAddressX
*ActiveChart.SeriesCollection(1).Values = sAddressY
*On Error Resume Next
*ActiveChart.SeriesCollection(1).Name = "=Empl_Hrs!R1C3"
*Dim Thomas As String
*Thomas = "hi"

*Range("B3").Select
Application.ScreenUpdating = True- Hide quoted text -

- Show quoted text -

How I redesign my macro to collect smaller amounts of data and then
join them at the end. The issue that I face is that the managers like
the format that they currently have and I do not have a choice of
making the data continous.

Thank you for all of your help

Post on top to make it easier to read the thread in sequence.

You have the ability to make the data contiguous, and still keep the
managers happy. Make a summary range that contains links or formulas to the
managers' data, but that is properly formatted for the chart. It can be on
another sheet, hidden if that shuts up the bosses.

I advise people to spend five minutes on their data, because it will save
them five hours of frustration.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
...
On Jan 21, 1:01 pm, "Jon Peltier"
wrote:
Post on top, so it's easier to read the thread in sequence.

How can have my macro split the vairable
into something less and then join it into one
large string at the end

If the issue is string length, why would forming a large string at the end
be better than at the beginning?

You should strive to use contiguous ranges as your chart's source data.
Sometimes this means setting up a summary range that the chart uses.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...
On Jan 21, 11:55 am, "Keith R" wrote:





Have you tested to see if it is a string length issue? E.g., if you
rename
your sheet Empl_Hrs and shorten your strings below, does that fix the
problem?

Keith

however, your code ends wrote in message

...

OK, I have an interesting issue. For the most part my macro seems to
work for creating and updating charts dynamically. But I am running
into an issue. If the chart data gets too large then it drops some of
the selections and errors out.

The following is a string in my macro for the series collection:

X = "=Employee_Hours!R652C1,Employee_Hours!R655C1,Empl oyee_Hours!
R660C1,Employee_Hours!R667C1,Employee_Hours!R670C1 ,Employee_Hours!
R673C1,Employee_Hours!R676C1,Employee_Hours!R679C1 ,Employee_Hours!
R682C1,Employee_Hours!R685C1,Employee_Hours!R688C1 "

Y = "=Employee_Hours!R653C4,Employee_Hours!R656C4,Empl oyee_Hours!
R661C4,Employee_Hours!R668C4,Employee_Hours!R671C4 ,Employee_Hours!
R674C4,Employee_Hours!R677C4,Employee_Hours!R680C4 ,Employee_Hours!
R683C4,Employee_Hours!R686C4,Employee_Hours!R689C4 "

This shows correctly when I debug it, but I get an error. If I resume
next and ignore the error and view the chart source I get the
following:

Y = =Employee_Hours!$D$653,Employee_Hours!$D$656,Emplo yee_Hours!$D
$661,Employee_Hours!$D$668,Employee_Hours!$D$671,E mployee_Hours!$D
$674,Employee_Hours!$D$677,Employee_Hours!$D$680,E mployee_Hours!$D
$683,Employee_Hours!$D$686 Employee_Hours!

X = =Employee_Hours!$A$652,Employee_Hours!$A$655,Emplo yee_Hours!$A
$660,Employee_Hours!$A$667,Employee_Hours!$A$670,E mployee_Hours!$A
$673,Employee_Hours!$A$676,Employee_Hours!$A$679,E mployee_Hours!$A
$682,Employee_Hours!$A$685 Employee_Hours!

The issue for some odd reason or another is the last part of these
entries. They are missing part of the string (It is missing the comma
and the last selection).

It does not happen on all of the employee data when I select them. I
am not sure what is happening being that if I modify the chart
selection and fix it manually it will display correctly

Can someone help me understand why this is happening- Hide quoted
text -

- Show quoted text -

I just tried that, and that seems to be my issue. How can have my
macro split the vairable into something less and then join it into one
large string at the end

Here is my current code:

Dim rDataX As Range
Dim rDataY As Range
Dim wsData As Worksheet
Dim sAddressX As String
Dim sAddressY As String
Dim iArea As Long
Dim cName As String
Dim scName As String
Dim rgA1 As String
Dim rgA2 As String
Dim rgD1 As String
Dim rgD2 As String
Dim rngTotal As String
Dim cDataX As String
Dim cDataY As String
Application.ScreenUpdating = False
rgA1 = ""
rgD1 = ""
rgA2 = ""
rgD2 = ""
rngTotal = ""
cDataX = ""
cDataY = ""
Application.Worksheets("Employee_Charts").Activate

cName = Range("B3").Value
Sheets("Empl_Hrs").Select
Range("A2").Select

Do While ActiveCell.Value < "" Or _
ActiveCell.Offset(0, 1).Value < ""
scName = ActiveCell.Value
If UCase(scName) = UCase(cName) Then
ActiveCell.Offset(1, 0).Select
rgA1 = ActiveCell.Row
rgD1 = ActiveCell.Row
Do While ActiveCell.Offset(0, 1).Value < ""
If ActiveCell.Offset(0, 1).Value = "Total Hours" Then
If Not cDataX < "" Then
cDataX = "A" & ActiveCell.Offset(-1, 0).Row
Else
cDataX = cDataX & "," & "A" & ActiveCell.Offset(-1, 0).Row
End If
If Not cDataY < "" Then
cDataY = "D" & ActiveCell.Row
Else
cDataY = cDataY & "," & "D" & ActiveCell.Row
End If
End If
If ActiveCell.Offset(0, 1).Value = "Total Working Hours" Then
rgA2 = ActiveCell.Offset(-2, 0).Row
rgD2 = ActiveCell.Offset(-2, 0).Row
End If
ActiveCell.Offset(1, 0).Select
Loop
End If
ActiveCell.Offset(1, 0).Select
Loop
If Not cDataX < "" Then
Sheets("Employee_Charts").Select
MsgBox "No Data Found." & Chr(10) & "Have a Nice Day!",
vbInformation

Exit Sub
End If
rgA1 = "A" & rgA1 & ":" & "A" & rgA2
rgD1 = "C" & rgD1 & ":" & "C" & rgD2
rngTotal = rgA1 & "," & rgD1
Sheets("Employee_Charts").Select
Set wsData = Worksheets("Empl_Hrs")
Set rDataX = wsData.Range(cDataX)
Set rDataY = wsData.Range(cDataY)

For iArea = 1 To rDataX.Areas.Count
sAddressX = sAddressX & wsData.Name & "!"
sAddressX = sAddressX & rDataX.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressX = "=" & Left(sAddressX, Len(sAddressX) - 1)

For iArea = 1 To rDataY.Areas.Count
sAddressY = sAddressY & wsData.Name & "!"
sAddressY = sAddressY & rDataY.Areas(iArea).Address(, , xlR1C1) &
","
Next
sAddressY = "=" & Left(sAddressY, Len(sAddressY) - 1)

ActiveSheet.ChartObjects.Select
ActiveChart.ChartTitle.Text = cName & Chr(10) & "Working Hours
Summary Report"
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = sAddressX
ActiveChart.SeriesCollection(1).Values = sAddressY
On Error Resume Next
ActiveChart.SeriesCollection(1).Name = "=Empl_Hrs!R1C3"
Dim Thomas As String
Thomas = "hi"

Range("B3").Select
Application.ScreenUpdating = True- Hide quoted text -

- Show quoted text -

How I redesign my macro to collect smaller amounts of data and then
join them at the end. The issue that I face is that the managers like
the format that they currently have and I do not have a choice of
making the data continous.

Thank you for all of your help