Thanks for your help. However, I am getting the following error on the FOR
line: Runtime Error -- Method 'Range' object '_Worksheet' failed. Below is
my current code:

Private Sub CommandButton11_Click()
Dim yr, yrdiff As Long
Dim cv, holamt, ha As Variant
Dim hd, pd As Date
Dim rng, Cell As Range
USERINPUT:
yr = InputBox("Enter year. (Example: 2006, 2007, 2008, etc.)")
If yr < 2006 Then
MsgBox ("Invalid Entry. Year must be greater than 2005.")
GoTo USERINPUT
End If
yrdiff = yr - 2006
Set rng = Worksheets("PCEMS").Cells(8 + (58 * yrdiff), 15).Resize(52)
MsgBox rng.Address
For Each Cell In Range(rng) <---- ERROR on this line (highlighted
yellow)
'cv = Cell.Value
MsgBox "test"
Next Cell
End Sub

Thanks,
Les

"Bob Phillips" wrote:

I am assuming that the 67 should be 66?

Dim yr As Long
Dim yrdiff As Long
Dim begrng As Range

yr = InputBox("Supply the year")
yrdiff = yr - 2006
Set begrng = Cells(8 + (58 * yrdiff), 15).Resize(52)
MsgBox begrng.Address


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"WLMPilot" wrote in message
...
How do you reference a range that changes based on user input?

I have Worksheet("PCEMS") that has my work schedule for each year starting
with 2006. Each schedule is 52 rows and offset from beginning of one year
to
beginning of next year is 58 rows.

How do you reference a range that changes based on user input?

I ask user (me) for year via Inputbox. I take the answer and perform
following calculation: yrdiff = yr - 2006 This gives me an offset
factor.

I want to reference data in column O depending on year entered.
2006 (2006-2006 = factor of 0) = RANGE("O8:O59")
2007 (2007-2006 = factor of 1) = RANGE("O67:O117")
2008 (2008-2006 = factor of 2) = RANGE("O124:O175")

Since the range is based on what year the user enters, I need to know how
to
reference the range. I know this is not the correct format, but you will
see
how I use the factor. I will use 2006 as yr and yrdiff = (2006-2006) for
factor of 0.

begrng = Worksheets("PCEMS").Cells(8+(58*yrdiff),15)
endrng = begrng.Offset(59 + (58 * yrdiff), 0)

How can I get the begrng and endrng to be one like Range("O8:O59) so that
I
can execute a "For Each Cell In Range _______" loop?

Thanks,
Les