Re: Excel Chart Macro Issue

On Jan 21, 1:01 pm, "Jon Peltier" <jonxlmv...@xxxxxxxxxxxxxxxxxxx>
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
_______

<whitethoma...@xxxxxxxxx> wrote in message

news:8d3e24da-5d07-485c-b5df-25444623c2f6@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jan 21, 11:55 am, "Keith R" <ker...@xxxxxxxxxxxxxxxx> wrote:





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

Keith

however, your code ends with<whitethoma...@xxxxxxxxx> wrote in message

news:9a173072-48d2-4eea-acef-d9fa9c7ea9cf@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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,Employee_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,Employee_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,Employee_Hours!$D
$661,Employee_Hours!$D$668,Employee_Hours!$D$671,Employee_Hours!$D
$674,Employee_Hours!$D$677,Employee_Hours!$D$680,Employee_Hours!$D
$683,Employee_Hours!$D$686 Employee_Hours!

X = =Employee_Hours!$A$652,Employee_Hours!$A$655,Employee_Hours!$A
$660,Employee_Hours!$A$667,Employee_Hours!$A$670,Employee_Hours!$A
$673,Employee_Hours!$A$676,Employee_Hours!$A$679,Employee_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 Work***
  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)

 Active***.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
.