Have searched through all the posts, but had no luck finding an existin
answer.

Here's the part of the code that is the problem. The worksheet
always contains one word of text in column 1. The variable ID i
intended to store that text, to be used later to create a chart title.


Dim Flag As Boolean
Dim i As Integer
Dim Start As Integer
Dim ID As Range
Dim sheetname As String

sheetname = ActiveSheet.Name
ActiveSheet.Select
Range("A1").Select
For i = 6 To 100
If Flag = False Then
Start = i
Set ID = Range(Cells(Start, 1), Cells(Start, 1))
Flag = True
End If

Have also tried changing the following two lines in the code:

Dim ID As String
ID = Range(Cells(Start, 1), Cells(Start, 1))

The first time through, the variable ID works fine and the chart i
created. However, when the text in column 1 changes, the error messag
"'Cells' of object _Global failed" shows on the assignment statemen
for the ID variable.

Any help for a newbie to this forum would certainly be appreciated

--
Message posted from http://www.ExcelForum.com

engineer

Where is this code - standard module, behind an activeX control, userform,
sheet event?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"engineer " wrote in message
...
Have searched through all the posts, but had no luck finding an existing
answer.

Here's the part of the code that is the problem. The worksheet
always contains one word of text in column 1. The variable ID is
intended to store that text, to be used later to create a chart title.


Dim Flag As Boolean
Dim i As Integer
Dim Start As Integer
Dim ID As Range
Dim sheetname As String

sheetname = ActiveSheet.Name
ActiveSheet.Select
Range("A1").Select
For i = 6 To 100
If Flag = False Then
Start = i
Set ID = Range(Cells(Start, 1), Cells(Start, 1))
Flag = True
End If

Have also tried changing the following two lines in the code:

Dim ID As String
ID = Range(Cells(Start, 1), Cells(Start, 1))

The first time through, the variable ID works fine and the chart is
created. However, when the text in column 1 changes, the error message
"'Cells' of object _Global failed" shows on the assignment statement
for the ID variable.

Any help for a newbie to this forum would certainly be appreciated!


---
Message posted from http://www.ExcelForum.com/

Found the answer in one of Stephen Bullen's replies on another thread
and it works:

I "fully qualified" the range and cells using:

With ActiveSheet
ID = .Range(.Cells(Start, 1), .Cells(Start, 1))
End With


BTW, this is a standard module.


Thanks

--
Message posted from http://www.ExcelForum.com

Okay, tried Stephen Bullen's method from another thread, and it work
fine!

You haven't fully-qualified the Cells calls:

Set srceRange = Sheetbasis.Range(Sheetbasis.Cells(2, 2), _
Sheetbasis.Cells(8, 2))

Or:

With Sheetbasis
Set srceRange = .Range(.Cells(2, 2), .Cells(8, 2))
End With


So simply adding the spreadsheet name before the range and cell
works:

With ActiveSheet
ID = .Range(.Cells(Start, 1), .Cells(Start, 1))
End With

This frustrating part of this is that at other times the code work
without fully qualifying the range and cells.

Sorry to waste anyone's time..

--
Message posted from http://www.ExcelForum.com