Macro stops before beginning.

[Frank Kabel]

Hi Alex
you may post the relevant part of your code which causes this error

 

[y]

I receive this message:

"I cannot found method or data member"

I hope it is clear because I'm working with Italian VBA for excel and I
translated the message literally.

Ciao Alex.

 

[y]

Here it is:

Sub Macro4_2()

Dim sArea, sXValues As Range
Dim i As Integer
Dim Nseries As Integer
Dim myChart As Chart
Dim sSTR As String

Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Rem MsgBox sArea.Address
Set sXValues = Application.InputBox(prompt:="Select XValues:", Type:=8)
Set sYValues = Application.InputBox(prompt:="Select YValues:", Type:=8)

Rem MsgBox "=Foglio1!" & sXValues.Offset(i - 1, 0).Resize(1, sArea.Columns.Count).Address
Rem Stop
Nseries = sArea.Rows.Count - 1
Set myChart = Charts.Add
With myChart
Rem .SetSourceData Source:=sArea
.Name = "Pippo"
Rem For i = 1 To Nseries
Rem .SeriesCollection.NewSeries
Rem Next
.Sheets("Foglio1").Activate
i = 1
For Each c In .SeriesCollection
sSTR = "=Foglio1!" & sArea.Offset(i - 1, 0).Resize(1, sArea.Columns.Count).Address
MsgBox sSTR
c.Values = sSTR
c.Name = "=Foglio1!" & sYValues(i)
c.XValues = sXValues
i = i + 1
Next
.ChartType = xlSurface
End With
End Sub

 

[Frank Kabel]

Hi
just some first notes without testing the functionality:
1. Add the line
Option Explicit
at the beginning of your module. You have some variables which are not
defined (e.g. 'c' and sYValues)

2. The first varriable declaration 'Dim sArea, sXValues As Range' only
defines the second variable as range. The first 'sArea' is defined as
variant. You may use 'Dim sArea as range, sXValues As Range'

3. The line
..Sheets("Foglio1").Activate
is not allowed within this 'with' statement. You probably meant
Sheets("Foglio1").Activate
Additonal note: There's probably no need to activate the sheet. Just
set an object reference to this sheet and use this reference

 

[y]

Hi
just some first notes without testing the functionality:
1. Add the line
Option Explicit
at the beginning of your module. You have some variables which are not
defined (e.g. 'c' and sYValues)

2. The first varriable declaration 'Dim sArea, sXValues As Range' only
defines the second variable as range. The first 'sArea' is defined as
variant. You may use 'Dim sArea as range, sXValues As Range'

3. The line
.Sheets("Foglio1").Activate
is not allowed within this 'with' statement. You probably meant
Sheets("Foglio1").Activate
Additonal note: There's probably no need to activate the sheet. Just
set an object reference to this sheet and use this reference

Thanks Frank, I activate the sheet to compare the msg in msgbox with the data spread in the sheet.

Thanks, Alex.

 

[y]

When I use c in

"for each c in ..."

what kind of type I must declare for c?
Thanks, Alex

 

[Frank Kabel]

Hi
as the object you loop through or as variant

 

[Yan-Hong Huang[MSFT]]

Hi,

For an example, if For Each c In .SeriesCollection

we define c as Series, since each item in SeriesCollection is a Series.

Does that answer your question?

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.