W
WLMPilot
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 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