Excel VBA - Storing text in a variable, and "'Cells' of object _Global failed"

  • Thread starter Thread starter engineer
  • Start date Start date
E

engineer

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
 
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..
 
engineer

Where is this code - standard module, behind an activeX control, userform,
sheet event?
 
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
 
Back
Top