Defining a Range

[Ric]

Hi, i'm trying to define a range with variables so I can search down a page
and produce graphs till an empty cell is found so it stops the loop but i'm
getting a "run time error '1004': Application-defined or object-defined
error".

My code so far;

Sub Produce_Cum_Graph()
'
' Produce a cumulative graph
' Macro recorded 30/03/04 by Ric
'
'
' Select sheet from where data is to be used
Sheets("cum_data").Select

' define variables
Start = 2 ' Used for row 2
SRow = 1
SCloumn = 3
ERow = 5
EColumn = 39
t1 = Cells(Start, 1).Value ' Contract Name
t2 = Cells(Start, 2).Value ' Contract Number

With Worksheets("cum_data")
Set MyRange = Range(Cells(SRow, SColumn), Cells(ERow, EColumn))
' Slecet range
End With

TheTitle = t2 & " " & "-" & " " & t1 ' Adds Contract Number & space
& - & space & Contract name together
Fname = t2 ' This will be filename of Graph
Call cum_graph ("MyRange", TheTitle", "Fname")

End Sub

cum_graph is a recorded Macro of creating a graph.
MyRange is interposed instead of an actual cell reference in cum_graph.
TheTitle is interposed instead of the typed title while creating the graph.

The error appears at line "Set MyRange..."

Am I going about this in totaly the wrong way to generate a range?

Ric

 

[Bob Phillips]

Just glanced at it, but try

With Worksheets("cum_data")
Set MyRange = .Range(.Cells(SRow, SColumn), .Cells(ERow, EColumn))
' Slecet range
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ric]

I've tried that from the help file already bob but i've tried it again
anyway. I still get the same error message as before. The variables in ()
show the correct values while I go through line by line but when i hover the
mouse over "MyRange" it shows "empty" as the value. I just don't think it
will accept the way i've put it together to make a Range.

("C1:AM5") is the range I am looking to call but how could I increment that
in a loop if next t1=true (it would have a range reference of ("C6:AM10")
for next )

Regards

Ric

 

[Bob Phillips]

Ric,

It is all down to a simple typo.

SCloumn = 3

and then use

Set MyRange = .Range(.Cells(SRow, SColumn), .Cells(ERow, EColumn))

not they are not the same, so sColumn is empty when you come to use it.

You should put Option Explicit at the top of your code, and this would force
you to declare your variables, and then these sort of errors would be caught
for you


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[ric]

Yeah bob I noticed that one myself after posting, but thanks. It now runs
past that line but MyRange still equals "Empty".

Still getting error 1004 but with "method 'Range' of object '_Global'
Failed" message instead when it comes to select the range in the graph
section.

Ric