[VBA] Convert string to Range

[Denis]

Hi,
I would like to know how I can convert a string in a range.

I write what I did:
I have one sheet with inside four charts (grafichs).
In this charts I have only to insert the source data.

<---------------------------------------------------------------------->
Private Sub Crea_grafico(foglio As String, tabella As String,
nome_tabella As String, foglio2 As String, marca As String)
Dim WB As Workbook
Dim SH As Worksheet
Dim area_grafico As Range
Dim area As String

Set WB = Workbooks(nuova_cartella)
Set SH = WB.Sheets(foglio)

' Here I determine the variable area
' area as this kind of value "I15,K15,P15,Z15,I39,K39,P39,Z39"
<------- I checked it with debug and it's ok

Set area_grafico = SH.Range(area)
' Here the macro stops with a '1004 Error'
' The strange behavoiur is that after it stops, I press F8 and the
instruction is ok and the macro goes on very well.

Sheets(foglio_grafici).ChartObjects("Grafico 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=SH.Range(area), PlotBy:=xlRows
<---------------------------------------------------------------------->

I use Excel 2003 SP2 (Italian version)
Who can help me??

Thanks in advance!!!!

--
Ciao Denis

Arriva un momento nella vita in cui non rimane altro da fare che
percorrere la propria strada fino in fondo...
Quello e' il momento di inseguire i propri sogni, quello e' il momento
di prendere il largo, forti delle proprie convinzioni.

 

[Dave Peterson]

Without testing...

The worksheet that owns the thing that should be activated has to be active.
Maybe it's as simple as making sure you're on the correct worksheet first.

application.goto Sheets(foglio_grafici).range("a1")
Sheets(foglio_grafici).ChartObjects("Grafico 2").Activate

===
You could also select the workbook, then select the sheet, too:

with sheets(foglio_grafici)
.parent.activate
.select
.chartobjects("grafico 2").activate
end with

 

[Denis]

Dave Peterson ha scritto:

The worksheet that owns the thing that should be activated has to be active.
Maybe it's as simple as making sure you're on the correct worksheet first.

Yes I'm on the correct worksheet.

application.goto Sheets(foglio_grafici).range("a1")

Why this??

Sheets(foglio_grafici).ChartObjects("Grafico 2").Activate

I do this.

with sheets(foglio_grafici)
.parent.activate
.select
.chartobjects("grafico 2").activate
end with

The error is always on SetSourceData

--
Ciao Denis

Arriva un momento nella vita in cui non rimane altro da fare che
percorrere la propria strada fino in fondo...
Quello e' il momento di inseguire i propri sogni, quello e' il momento
di prendere il largo, forti delle proprie convinzioni.

 

[Dave Peterson]

I couldn't tell which line had the problem.

In a different forum for a different problem (working with pivottables), someone
said that adding this line right before the offending line helped them.

Application.Wait Now + TimeSearial(0,0,1)

It's just a way to pause for a second.

I don't know if it'll help you, though.