Help - Type mismatch when running loop with strings from arrays

[Marie J-son]

Hi,
I need to combine a number of different strings to run throught 3x3x3
chartobjects to rename their titles. The chartobjects are named like
"C_CF_SB_31" and I have their ChartTitles named similar but with at "T"
first instead, like T_CF_SB_31 .

The "T_CF_SB_31" is a name of a cell(range) that contain the correct title
of the shart

This is my code and it stoppe at line : "With Sheet1.ChartObjects("C_" & cht
& "_" & qlt & "_" & alt)" with error "incompatible types/type mismatch... I
guess it is the mix between variant and strings, or what? How should it be?
Please help, any...

Sub ChartObjectTitles()
Dim alt As Variant
Dim cht As Variant
Dim qlt As Variant

alt = Array("21", "31", "41")
cht = Array("ROI", "CF", "Q")
qlt = Array("SB", "SBA", "S")

With Sheet1.ChartObjects("C_" & cht & "_" & qlt & "_" & alt)
.HasTitle = True
.ChartTitle.Characters.Text = Sheet1.Range("T_" & cht & "_" & qlt & "_"
& alt)
End With

End Sub

Regards

 

[Dave Peterson]

alt, cht, and qlt are arrays.
So you have to pick out the element that you want from each array.

Kind of like:

Option Explicit
Sub ChartObjectTitles()

Dim alt As Variant
Dim cht As Variant
Dim qlt As Variant

Dim aCtr As Long
Dim cCtr As Long
Dim qCtr As Long

Dim testChartObject As ChartObject

alt = Array("21", "31", "41")
cht = Array("ROI", "CF", "Q")
qlt = Array("SB", "SBA", "S")

For aCtr = LBound(alt) To UBound(alt)
For cCtr = LBound(cht) To UBound(cht)
For qCtr = LBound(qlt) To UBound(qlt)
Set testChartObject = Nothing
On Error Resume Next
Set testChartObject _
= Sheet1.ChartObjects("C_" & cht(cCtr) & "_" _
& qlt(qCtr) & "_" & alt(aCtr))
On Error GoTo 0

If testChartObject Is Nothing Then
'it's not there, what should be done???
Else
With testChartObject
.HasTitle = True
.ChartTitle.Characters.Text _
= Sheet1.Range("T_" & cht(cCtr) & "_" _
& qlt(qCtr) & "_" & alt(aCtr))
End With
End If
Next qCtr
Next cCtr
Next aCtr

End Sub

I didn't set up a test workbook for this, but the code did compile ok.

 

[Marie J-son]

Thank you, Dave

once more you have helped me and I find , as always, one more function,
property or object I hasn't used before. Arrays has always been a weak point
for me. As you saw, I kind of thought that I shouldn't need to loop through
them if I used arrays...

Except for one correction in your code, it work out fine! Actually, the
error probably originated from my code in the beginning...
ChartTitle has not chartobject as parent, but a chart. Therefore "With
testChartObject." should be "With testChartObject.Chart".

Complete code:

Option Explicit
Sub ChartObjectTitles()

Dim alt As Variant
Dim cht As Variant
Dim qlt As Variant

Dim aCtr As Long
Dim cCtr As Long
Dim qCtr As Long

Dim testChartObject As ChartObject

alt = Array("21", "31", "41")
cht = Array("ROI", "CF", "Q")
qlt = Array("SB", "SBA", "S")

For aCtr = LBound(alt) To UBound(alt)
For cCtr = LBound(cht) To UBound(cht)
For qCtr = LBound(qlt) To UBound(qlt)
Set testChartObject = Nothing
On Error Resume Next
Set testChartObject _
= Sheet1.ChartObjects("C_" & cht(cCtr) & "_" _
& qlt(qCtr) & "_" & alt(aCtr))
On Error GoTo 0

If testChartObject Is Nothing Then
'it's not there, what should be done???
Else
With testChartObject.Chart
.HasTitle = True
.ChartTitle.Characters.Text _
= Sheet1.Range("T_" & cht(cCtr) & "_" _
& qlt(qCtr) & "_" & alt(aCtr))
End With
End If
Next qCtr
Next cCtr
Next aCtr

End Sub

/Regards

 

[Dave Peterson]

I did steal your original code and missed that problem--but you found it and
that's good!