Need help using a variable in Range command
 

Hello,

I am trying to select a variably sized range in column D using th
following code:

Sub Sta1CycleTimeFill()
Dim Sta1EndRangeValue As Integer

Sta1EndRangeValue = Range("g2").Value
MsgBox Sta1EndRangeValue
Sta1EndRange = "d" & Sta1EndRangeValue
MsgBox Sta1EndRange
Range(d2, "Sta1EndRange").Select
'Range("D2").Offset(Sta1EndRange, 0).Select
'Selection.Copy
'Range("I2").Select
'ActiveSheet.Paste


End Sub

With the current data that I have the value in G2 is 347, when I ru
the macro the first message box shows 347 and the second message bo
shows d347, but then I get a Run-time error '1004':
Method 'Range' of object '_Global' failed for the Range(d2
"Sta1EndRange").Select line.

How do I go about making the first and last cells in the range comman
be variables? After this data is copied & pasted to I2 I then need t
find the next variably sized chunk and copy and paste to J2 and so o
for a total of 7 times. I know how big each range is because in cell
G2:G8 I have the formulas
=COUNTIF(A:A, "sta1")
=COUNTIF(A:A, "sta2")
=COUNTIF(A:A, "sta3")
=COUNTIF(A:A, "sta4")
=COUNTIF(A:A, "sta5")
=COUNTIF(A:A, "sta6")
=COUNTIF(A:A, "sta7")
respectively.

Thank you

--
cbeeb
-----------------------------------------------------------------------
cbeebe's Profile: http://www.excelforum.com/member.php...fo&userid=1360
View this thread: http://www.excelforum.com/showthread.php?threadid=27563

Need help using a variable in Range command
 

Try this out - I think it is what your trying to do - one
mistake was putting a variable in quotes (this makes it
appear as a string)

Sub Sta1CycleTimeFill()
Dim Sta1EndRangeValue As Integer

Sta1EndRangeValue = Range("g2").Value
MsgBox Sta1EndRangeValue
Sta1EndRange = "d" & Sta1EndRangeValue
MsgBox Sta1EndRange
Range("d2", Sta1EndRange).Select
'Range("D2").Offset(Sta1EndRange, 0).Select
'Selection.Copy
'Range("I2").Select
'ActiveSheet.Paste


End Sub
-----Original Message-----

Hello,

I am trying to select a variably sized range in column D
using the
following code:

Sub Sta1CycleTimeFill()
Dim Sta1EndRangeValue As Integer

Sta1EndRangeValue = Range("g2").Value
MsgBox Sta1EndRangeValue
Sta1EndRange = "d" & Sta1EndRangeValue
MsgBox Sta1EndRange
Range(d2, "Sta1EndRange").Select
'Range("D2").Offset(Sta1EndRange, 0).Select
'Selection.Copy
'Range("I2").Select
'ActiveSheet.Paste


End Sub

With the current data that I have the value in G2 is 347,
when I run
the macro the first message box shows 347 and the second
message box
shows d347, but then I get a Run-time error '1004':
Method 'Range' of object '_Global' failed for the Range
(d2,
"Sta1EndRange").Select line.

How do I go about making the first and last cells in the
range command
be variables? After this data is copied & pasted to I2 I
then need to
find the next variably sized chunk and copy and paste to
J2 and so on
for a total of 7 times. I know how big each range is
because in cells
G2:G8 I have the formulas
=COUNTIF(A:A, "sta1")
=COUNTIF(A:A, "sta2")
=COUNTIF(A:A, "sta3")
=COUNTIF(A:A, "sta4")
=COUNTIF(A:A, "sta5")
=COUNTIF(A:A, "sta6")
=COUNTIF(A:A, "sta7")
respectively.

Thank you.


--
cbeebe
----------------------------------------------------------
--------------
cbeebe's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=13601
View this thread:
http://www.excelforum.com/showthread...hreadid=275636

.