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

Since you already have your beginning cell and ending cell
begrng = Worksheets("PCEMS").Cells(8+(58*yrdiff),15)
endrng = begrng.Offset(59 + (58 * yrdiff), 0)

you could use:
For Each rngCell in Range(begrng, endrng).Cells

Next rngCell



"WLMPilot" wrote:

> 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

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" <(E-Mail Removed)> wrote in message
news:14541CD9-3C3E-41B4-91B5-(E-Mail Removed)...
> 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

Maybe...

Dim myYear As Long
Dim myRng As Range
Dim myCell As Range
Dim FirstYear As Long

FirstYear = 2006

myYear = 2008 'I'm not sure where you get this.

Set myRng = Worksheets("sheet1") _
.Cells(8 + (58 * (myYear - FirstYear - 1)), "o") _
.Resize(52, 1)

MsgBox myRng.Address



WLMPilot wrote:
>
> 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

--

Dave Peterson

You could use the Case method where for example:

Case = Year

Case Is 2006
myRange = Range("O8:O59")

Case Is 2007
myRange = Range("O67:O117")

Case Is 2007
myRange = Range("O124:O175")

For Each c In myRange
'Do events
Next

Pick up your year from the variable that stores the User's entry.

"WLMPilot" wrote:

> 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

Just another variation without error checking:

Sub Demo()
Dim AllYears As Range
Dim Data As Range
Dim Year As Long

Year = 2007

Set AllYears = [o8:O59, O67:O117, O124:O175]
Set Data = AllYears.Areas(Year - 2005)
Data.Select
End Sub

--
Dana DeLouis



"WLMPilot" <(E-Mail Removed)> wrote in message
news:14541CD9-3C3E-41B4-91B5-(E-Mail Removed)...
> 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

Thanks. I did not know I could write RANGE like you did. I was getting
ready to try the loop out and decided to display the value of begrng and
endrng and I got an error -- "Object Required". Below is the code I
currently have. Can you tell me why I am getting the error? I have not
utilized some of the variables yet.

Private Sub CommandButton11_Click()
Dim yr, yrdiff, cv, holamt, ha As Integer
Dim hd, pd As Date
Dim begrng, endrng, rngCell 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
begrng = Worksheets("PCEMS").Cells(8, 15)
endrng = begrng.Offset(59 + (58 * yrdiff), 0)
MsgBox begrng
MsgBox endrng
'For Each rngCell In Range(begrng, endrng).Cells

End Sub

Thanks,
Les

"JMB" wrote:

> Since you already have your beginning cell and ending cell
> begrng = Worksheets("PCEMS").Cells(8+(58*yrdiff),15)
> endrng = begrng.Offset(59 + (58 * yrdiff), 0)
>
> you could use:
> For Each rngCell in Range(begrng, endrng).Cells
>
> Next rngCell
>
>
>
> "WLMPilot" wrote:
>
> > 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

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" <(E-Mail Removed)> wrote in message
> news:14541CD9-3C3E-41B4-91B5-(E-Mail Removed)...
> > 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
>
>
>

rng is already a range.

You want:
For Each Cell In rng.cells

ps. this code:


WLMPilot wrote:
>
> 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" <(E-Mail Removed)> wrote in message
> > news:14541CD9-3C3E-41B4-91B5-(E-Mail Removed)...
> > > 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
> >
> >
> >

--

Dave Peterson

ps: This code:

Dim yr, yrdiff As Long
Dim cv, holamt, ha As Variant
Dim hd, pd As Date
Dim rng, Cell As Range

is the same as:

Dim yr as variant, yrdiff As Long
Dim cv as variant, holamt as variant, ha As Variant
Dim hd as variant, pd As Date
Dim rng as variant, Cell As Range

probably not what you intended.

WLMPilot wrote:
>
> 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" <(E-Mail Removed)> wrote in message
> > news:14541CD9-3C3E-41B4-91B5-(E-Mail Removed)...
> > > 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
> >
> >
> >

--

Dave Peterson