Help - Type mismatch when running loop with strings from arrays

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

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 wrote:
>
> 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

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

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



Marie J-son wrote:
>
> 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