Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - Type mismatch when running loop with strings from arrays
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - Type mismatch when running loop with strings from arrays
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type Mismatch Error when running a SELECT SQL | Excel Programming | |||
Arrays and Strings | Excel Programming | |||
Arrays and Strings | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Type mismatch while running If statement | Excel Programming |