Thread: Connect data markers

Code:

Sub MakeData()

    Dim rngMinPoints As Range
    Dim rngMaxPoints As Range
    Dim rngMinDollars As Range
    Dim rngMaxDollars As Range
    Dim rngMinPay As Range
    Dim rngMaxPay As Range
    Dim rngOutput As Range
    Dim lngNGrades As Long
    Dim lngIndex As Long
    
    With Worksheets("Pay Grades")
        Set rngMinPay = .Range("C17")
        Set rngMaxPay = .Range("F17")
        lngNGrades = .Range("D16")
        Set rngMinPoints = .Range("B20").Resize(lngNGrades, 1)
        Set rngMaxPoints = rngMinPoints.Offset(0, 2)
        Set rngMinDollars = rngMinPoints.Offset(0, 4)
        Set rngMaxDollars = rngMinPoints.Offset(0, 5)
    End With
    
    Set rngOutput = Worksheets("BoxesScreen7").Range("A1")
    rngOutput.Offset(0, 0).Formula = "='" & rngMinPay.Parent.Name & "'!" & rngMinPay.Address
    rngOutput.Offset(1, 0).Formula = "='" & rngMinPay.Parent.Name & "'!" & rngMinPay.Address
    rngOutput.Offset(1, 1).Formula = "='" & rngMinDollars.Parent.Name & "'!" & rngMinDollars.Cells(1, 1).Address
    rngOutput.Offset(1, 2).Formula = "='" & rngMaxDollars.Parent.Name & "'!" & rngMaxDollars.Cells(1, 1).Address
    For lngIndex = 1 To lngNGrades
        rngOutput.Offset(lngIndex + 1, 0).Formula = "='" & rngMinPoints.Parent.Name & "'!" & rngMinPoints.Cells(lngIndex + 1, 1).Address
        rngOutput.Offset(lngIndex + 1, 1).Formula = "='" & rngMinDollars.Parent.Name & "'!" & rngMinDollars.Cells(lngIndex, 1).Address
        rngOutput.Offset(lngIndex + 1, 2).Formula = "='" & rngMaxDollars.Parent.Name & "'!" & rngMaxDollars.Cells(lngIndex + 1, 1).Address
    Next
    rngOutput.Offset(lngNGrades + 1, 0).Formula = "='" & rngMaxPay.Parent.Name & "'!" & rngMaxPay.Address
    rngOutput.Offset(lngNGrades + 2, 0).Formula = "='" & rngMaxPay.Parent.Name & "'!" & rngMaxPay.Address
    rngOutput.Offset(lngNGrades + 1, 2).Formula = "='" & rngMaxDollars.Parent.Name & "'!" & rngMaxDollars.Cells(lngNGrades, 1).Address

    rngOutput.Offset(1, 3).Resize(lngNGrades + 1, 1).FormulaR1C1 = "=RC[-1]-RC[-2]"
    rngOutput.Offset(1, 4).Resize(lngNGrades + 1, 1).FormulaR1C1 = "=RC[-4]-R[-1]C[-4]"
    rngOutput.Offset(1, 5).Resize(lngNGrades + 1, 1).FormulaR1C1 = "=R[1]C[-5]-RC[-5]"

End Sub

Can you post a workbook that demonstrates the problem

Lets worry about it when the error does appear consistently

Andy:

I have attached a screen print of the error when it happened. Thanks.

- Ravi

Originally Posted by Andy Pope

Lets worry about it when the error does appear consistently

Hi Andy:

Here is a situation where the graph was not displayed but came with the same error 1004. Clicking on help said something about macros, which were already enabled to run. In some instance there were messages that said that the worksheet was read only, thus not allowing any changes to be made. This happened on laptops, one with XP, one with Vista, one with 2003 excel one
with 2007. I don't have a cue as to what might be happening here.

I have attached the referenced worksheets for your review. Thanks.

- Ravi

you passworded the VBA so I can not see anything.

It throws an error when loading the splash screen.
It also errors when deleting series, although I don't know why it's deleting series.

Looks like you need to add some general error trapping into the code to check for certain states, such as no series.
You may also encounter problems trying to manipulate chart series that have no data.

You need to change the way you reference the chart as currently you are using a name that could change if you created another chart.

Code:

Sub ClearExistingSeriesScreen7Graph()
'
' ClearExistingSeriesScreen7Graph Macro
'

'
    With Sheets("Pay Grades").ChartObjects(1).Chart
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
    End With

End Sub

As for error when the workbook name changes that is because, again, you are using fixed names as the reference.

so instead of references like this

Code:

    Windows("CompSoftPhaseII.XLS").Activate

use

Code:

Thisworkbook.Activate

Find/Replace

Charts are completely new in xl2007. Some of the Object model uses new properties. And in places the object model is incomplete.
I don't think you will find a detailed list it's more a case of try it and see. Then fix the problems.