Hi all! I have run across an issue with data retrieval. At the company I
work for, the year runs from November 1, 2008-October 31, 2009. In one of
our forms, the user will enter a date such as 11/1/2008 in cell B2. Our path
for data retrieval is m:\2009\SALES TEAM\Midwest Territory.xls. For two
months (nov and dec) the year will be 2008. How can I change the mYear
variable below to accomodate the year 2008 when it actually resides in the
2009 folder? My apologies, I'm very new to excel and VBA. Thanks.

sdate = Range("B2").Value

mYear = Format(sdate, "yyyy")
mPath = "='m:\" & mYear & "\SALES TEAM\Midwest Territory.xls"
--
jenn k

Dim sDate As Date
Dim mYear As Long

sDate = ActiveSheet.Range("B2").Value
'for testing
'sDate = DateSerial(2008, 11, 1)

mYear = Year(sDate) - CBool((Month(sDate) 10))

MsgBox mYear

(In VBA, true is equal to -1)



jenn k wrote:

Hi all! I have run across an issue with data retrieval. At the company I
work for, the year runs from November 1, 2008-October 31, 2009. In one of
our forms, the user will enter a date such as 11/1/2008 in cell B2. Our path
for data retrieval is m:\2009\SALES TEAM\Midwest Territory.xls. For two
months (nov and dec) the year will be 2008. How can I change the mYear
variable below to accomodate the year 2008 when it actually resides in the
2009 folder? My apologies, I'm very new to excel and VBA. Thanks.

sdate = Range("B2").Value

mYear = Format(sdate, "yyyy")
mPath = "='m:\" & mYear & "\SALES TEAM\Midwest Territory.xls"
--
jenn k

--

Dave Peterson

Set up a variable in excel, Insert|Name|Define
YrFlder: =EDATE(B2,2)

Then change your variable in vba to the following:
sdate = Range(YrFldr).Value

I am sure there are other 'cleaner' ways, but this should accomplish what
you need it to do.

Hope this helps.

--
John C


"jenn k" wrote:

Hi all! I have run across an issue with data retrieval. At the company I
work for, the year runs from November 1, 2008-October 31, 2009. In one of
our forms, the user will enter a date such as 11/1/2008 in cell B2. Our path
for data retrieval is m:\2009\SALES TEAM\Midwest Territory.xls. For two
months (nov and dec) the year will be 2008. How can I change the mYear
variable below to accomodate the year 2008 when it actually resides in the
2009 folder? My apologies, I'm very new to excel and VBA. Thanks.

sdate = Range("B2").Value

mYear = Format(sdate, "yyyy")
mPath = "='m:\" & mYear & "\SALES TEAM\Midwest Territory.xls"
--
jenn k

Should be
YrFldr: =EDATE($B$2,2)
--
John C


"John C" wrote:

Set up a variable in excel, Insert|Name|Define
YrFlder: =EDATE(B2,2)

Then change your variable in vba to the following:
sdate = Range(YrFldr).Value

I am sure there are other 'cleaner' ways, but this should accomplish what
you need it to do.

Hope this helps.

--
John C


"jenn k" wrote:

Hi all! I have run across an issue with data retrieval. At the company I
work for, the year runs from November 1, 2008-October 31, 2009. In one of
our forms, the user will enter a date such as 11/1/2008 in cell B2. Our path
for data retrieval is m:\2009\SALES TEAM\Midwest Territory.xls. For two
months (nov and dec) the year will be 2008. How can I change the mYear
variable below to accomodate the year 2008 when it actually resides in the
2009 folder? My apologies, I'm very new to excel and VBA. Thanks.

sdate = Range("B2").Value

mYear = Format(sdate, "yyyy")
mPath = "='m:\" & mYear & "\SALES TEAM\Midwest Territory.xls"
--
jenn k

Thank you both very much for your help! I will give it a whirl.
--
jenn k


"John C" wrote:

Should be
YrFldr: =EDATE($B$2,2)
--
John C


"John C" wrote:

Set up a variable in excel, Insert|Name|Define
YrFlder: =EDATE(B2,2)

Then change your variable in vba to the following:
sdate = Range(YrFldr).Value

I am sure there are other 'cleaner' ways, but this should accomplish what
you need it to do.

Hope this helps.

--
John C


"jenn k" wrote:

Hi all! I have run across an issue with data retrieval. At the company I
work for, the year runs from November 1, 2008-October 31, 2009. In one of
our forms, the user will enter a date such as 11/1/2008 in cell B2. Our path
for data retrieval is m:\2009\SALES TEAM\Midwest Territory.xls. For two
months (nov and dec) the year will be 2008. How can I change the mYear
variable below to accomodate the year 2008 when it actually resides in the
2009 folder? My apologies, I'm very new to excel and VBA. Thanks.

sdate = Range("B2").Value

mYear = Format(sdate, "yyyy")
mPath = "='m:\" & mYear & "\SALES TEAM\Midwest Territory.xls"
--
jenn k