Help - Type mismatch when running loop with strings from arrays
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
|