View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips[_6_] Bob Phillips[_6_] is offline
external usenet poster
 
Posts: 11,272
Default Defining a Range

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" <richard.cravenatbaesystems.com wrote in message
...
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