VBA variable definition help needed.

  • Thread starter Thread starter jenn k
  • Start date Start date
J

jenn k

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"
 
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)
 
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.
 
Should be
YrFldr: =EDATE($B$2,2)
--
John C


John C said:
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.
Click to expand...
 
Back
Top