CODEWITHSUNDEEP
excelvbachartsexcel-formulaseries
Moldevort
Moldevort

Reputation: 193

Changing series formula with VBA. Error Code 1004

I'm working on an excel project that helps visualising blood work in generating a chart for each single value.

As I'm trying to prevent empty values in charts I added two sheets that actually manage the data ("#data") and charts ("#charts"). In "data" one enters values and dates, in "charts" you will see the charts (empty charts will not be displayed), "#data" helps generating series and "#charts" actually carries the charts.

At the moment I'm working on a VBA script that will help me duplicating a chart on "#charts" and changing series for the duplicates (I need about 100 of those). It works perfectly for the first duplicate but on the second I get a Run-time Error #1004: Application-defined of object-defined error. I assumed it could have to do with the series for the chart so I made the range for the series identical - unfortunately, that didn't solve the problem. 1st duplicate works perfectly, 2nd doesn't.

Here's the link to my file: http://ovh.to/ZkmYCUk

Script for Duplicating:

Sub tt()

    j = 3
    L = "C"
    s = 4 * (j - 1)

    Cr = Int((j - 1) / 4) + 1
    cc = (j - 1) Mod 4 + 1

    Dim oldc As ChartObject
    Dim newc As Object
    Set ws = Sheets("#charts")
    no = "CH_A"
    nn = "CH_" & L

    Set oldc = ws.ChartObjects(no)
    Set newc = oldc.Duplicate
    newc.Name = nn

    newc.Left = ws.Cells(Cr, cc).Left
    newc.Top = ws.Cells(Cr, cc).Top
    newc.Height = ws.Cells(Cr, cc).Height
    newc.Width = ws.Cells(Cr, cc).Width

    For k = 1 To newc.Chart.SeriesCollection.Count
        With newc.Chart.SeriesCollection(k)
            .Formula = Replace(.Formula, "A", L)
        End With
    Next k

    newc.Chart.ChartTitle.Text = "='#data'!$A$" & (s + 2)
End Sub

Series for charts:

A_L = IF(COUNT(data!$F$3:$S$3)>0; OFFSET('#data'!$B$1; 0; 0; 1; COUNT(data!$F$3:$S$3)); 0)
A_V = IF(COUNT(data!$F$3:$S$3)>0; OFFSET('#data'!$B$2; 0; 0; 1; COUNT(data!$F$3:$S$3)); 0)
A_M = IF(COUNT(data!$F$3:$S$3)>0; OFFSET('#data'!$B$3; 0; 0; 1; COUNT(data!$F$3:$S$3)); 0)
A_D = IF(COUNT(data!$F$3:$S$3)>0; OFFSET('#data'!$B$4; 0; 0; 1; COUNT(data!$F$3:$S$3)); 0)

B_L = IF(COUNT(data!$F$4:$S$4)>0; OFFSET('#data'!$B$5; 0; 0; 1; COUNT(data!$F$4:$S$4)); 0)
B_V = IF(COUNT(data!$F$4:$S$4)>0; OFFSET('#data'!$B$6; 0; 0; 1; COUNT(data!$F$4:$S$4)); 0)
B_M = IF(COUNT(data!$F$4:$S$4)>0; OFFSET('#data'!$B$7; 0; 0; 1; COUNT(data!$F$4:$S$4)); 0)
B_D = IF(COUNT(data!$F$4:$S$4)>0; OFFSET('#data'!$B$8; 0; 0; 1; COUNT(data!$F$4:$S$4)); 0)

C_L = IF(COUNT(data!$F$5:$S$5)>0; OFFSET('#data'!$B$9; 0; 0; 1; COUNT(data!$F$5:$S$5)); 0)
C_V = IF(COUNT(data!$F$5:$S$5)>0; OFFSET('#data'!$B$10; 0; 0; 1; COUNT(data!$F$5:$S$5)); 0)
C_M = IF(COUNT(data!$F$5:$S$5)>0; OFFSET('#data'!$B$11; 0; 0; 1; COUNT(data!$F$5:$S$5)); 0)
C_D = IF(COUNT(data!$F$5:$S$5)>0; OFFSET('#data'!$B$12; 0; 0; 1; COUNT(data!$F$5:$S$5)); 0)

Upvotes: 1

Views: 1150

Answers (1)

burtelli
burtelli

Reputation: 153

If you use 

.FormulaR1C1 = Replace(.FormulaR1C1 , "A", L)

instead of 

.Formula = Replace(.Formula, "A", L)

the '1004' goes away. Beats me why but it's alway worth trying when '.formula' results in an error. You'll still have to put some thought into the positioning of the charts though. Currently they are all stacked on top of each other. But that should be doable.

Upvotes: 1

Related Questions