VBA variable definition help needed.

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
0
jennk (4)
9/5/2008 6:37:02 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
382 Views

Similar Articles

[PageSpeed] 20

    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
0
petersod (12005)
9/5/2008 7:31:24 PM
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
0
Utf
9/5/2008 7:36:08 PM
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
0
Utf
9/5/2008 7:39:01 PM
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
0
jennk (4)
9/5/2008 7:55:20 PM
Reply:

Similar Artilces:

need to reload 2007 home/student have prod key
where do i go to reload 2007 home/student i have product key On 2/04/2010 7:21 PM, howie2006 wrote: > where do i go to reload 2007 home/student i have product key Where is the CD? On 2/04/2010 7:21 PM, howie2006 wrote: > where do i go to reload 2007 home/student i have product key Sorry that was not very helpful. If you had the CD you would not be posting here. The only thing I could find was a 60 day Office professional trial. http://trial.trymicrosoftoffice.com/ HTH http://www.microsoft.com/office/downloads/ ...

Delete All Picture Objects Using VBA
All, I am looking for some code that will allow me to go in and delete all "Picture" objects from within a worksheet without deleting the rest of the data. Is this possible? Regards, Nathan various ways Sub ShapesCut() For Each S In ActiveSheet.Shapes S.Cut Next End Sub 'or Sub shapescut1() 'Tom Ogilvy ActiveSheet.Shapes.SelectAll Selection.Delete End Sub Sub ShapesALLinWorkbookDelete() 'Deletes all in WORKBOOK Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets sh.DrawingObjects.Delete Next sh end sub -- Don Guillett SalesAid Software donaldb@28...

Help Sendobjest
I am hopping some one can help me I have an application in Access 2000 which has been running on XP and XP professional for a very long time every morning this application creates a list of reminders it then creates an E-Mail for each reminder and with a loop it sends all the e-mails one at a time the code is DoCmd.SendObject acSendNoObject, , acFormatTXT, StrFullAddress, , , strMailSubject, strMailMsg, False All of a sudden a couple of months ago I noticed that it was sending the first mail ok with the usual out look message and then it just looped through the rest but did not send...

meeting invites now show up as emails only
As of very recently, Outlook invites sent from my work calendar to my home calendar (both Outlook, although work Outlook is 2003 I think) now only show up as emails, not as calendar invites. Does anyone have an idea why this is happening - it used to work just fine. Fixes? ...

Please Help: Toolbars in DLL Dialog
Hello, Creating toolbars in an EXE app is a relative piece of cake. However, I am trying to insert a toolbar into a DLL dialog. This is how my DLL dialog works: 1) Contents of a function exporting a dialog AFX_MANAGE_STATE(AfxGetStaticModuleState()); CSvg* dlg; try { dlg = new CSvg(); dlg->Create(IDD_MAIN_FRAME); dlg->ShowWindow(SW_SHOW); } catch (CSvg *dlg) { dlg->PostNcDestroy(); delete dlg; } 2) Use a function in my Dialog class to insert a toolbar: int CSvg::OnCreate(LPCREATESTRUCT lpCreateStruct) { if (CDialog::OnCreate(lpCreateStruct) == -1) return -...

Transfer Files Using VBA
Hi, I'd like to transfer files from several drives on my pc to one cetnral location. From A:\Office1\ B:\Office2\ C:\Office3\ To D:\HeadQuarters\ My routine now is the following 1) Copy the files from drives: A, B, and C; and then 2) Paste the filesto the D drive 3) Delete the files permanently from drives A, B, and C. As you see this is a tedious routine, which I'd liket o avoid by using VBA. Can someone start me off with some code on how to accomplish this. Thanks so much in advance! Jrew Jrew, You could create a batch file (from the olden days) using XCOPY and desired bel...

NEED HELP-wrong formatting saved ??
After using word 2007 and saving a word file, it changed the formatting from the previous file (i think it was .wps microsoft works word processor) and now its all messed up.....I think I saved it in some other wrong encoding standard......now, whenever I open this file now half of it is in some weird looking unreadable characters....like this with wha捬屨捦ㅳ尠晡‰汜牴档晜獣‰歜牥楮杮尰扤档慜㍦㔱㔰楜獮獲摩㌱ㄳ〶‷਍灜牡素筽⩜灜獮捥癬ㅬ灜畮牣屭湰瑳牡ㅴ灜楮摮湥㝴〲灜桮湡⁧屻湰硴慴ges! That is what seems to happen once the system breaks its laws from the inside. ......couldnt find online solutions...does anyone have any suggestions or s...

I have over 100 excel sheets i need put in alphabetic order,how?
Background: I have an index page with hyperlinks to each sheet, each sheet has a COUNT function to add the entries and a SUM function on the index page which adds the result of each COUNT function, to do that I used "SUM(Birmingham:Worcester'!B2)" as each sheet has a geographical name a newly added sheet may not fall within the specified range and therefore may not be added. Is there a way to alphabetise these sheets? Rob, have a look here, http://www.cpearson.com/excel/sortws.htm -- Paul B Always backup your data before trying something new Please post any response to t...

need to allow negative quantitys on purchase orders
because our vendors list returns and exchanges on invoice i need the abillity to enter negative quantitys when recieving manually. any ideas? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us...

Help with creating a formula
Hello! I am trying to create a formula that will sum up certain criteria from one column as it relates to criteria in the same row in another column. For example, I want to sum up all the entries that begin with the letter "H" in column A as it relates to an entry of "2" from column B that is in the same row as the "H" entry from column A. COLUMN A COLUMN B H10 2 G01 2 H09 3 H15 2 H12 2 If there is such a formula...

NDR Help
Hi, I hope someone can help on this. A few people have complained they cannot e-mail to us always. They get an NDR. This is very intermittent it seems and not everyone is affected. They have forwarded the NDR they have received. I've attached it below. I can't work out if our mail server has sent that to them or if it is generated by their company e-mail server. This happens mainly with one company but i think it has happened to some others as well - i can't be sure but the one company mainly (or even only). It is odd in that they can e-mail and get through then do it aga...

Drag and drop help
I am trying to get drag and drop working between a few windows in my application. I can get the dragging started, but can't get away from the 'no-entry' mouse cursor. I have read through the MSDN and I have done the following in a CTreeView derived class called TreeCtrlArtifacts: void TreeCtrlArtifacts::OnTvnBegindrag(NMHDR *pNMHDR, LRESULT *pResult) { LPNMTREEVIEW pNMTreeView = reinterpret_cast<LPNMTREEVIEW>(pNMHDR); // TODO: Add your control notification handler code here CUIntArray arrayOfSelected; arrayOfSelected.Add(0); CMainFrame *main = (CMainFrame*) AfxGetMa...

Recordset Help Needed
I have the below code that works some of the times, but not all. Here is my issue. If there is no records in the table it works the first time. Then when I go to add another record it says "No Current Record". If I close the form and then re-open it, it work. I thought by adding the Set rs = Nothing it may help. It did seem to do anything. Private Sub TRAN_TYPE_DblClick(Cancel As Integer) Dim rs As DAO.Recordset Dim rs1 As DAO.Recordset 'Save any edits If Me.Dirty Then Me.Dirty = False End If 'Check there is an order in the form. If...

*CANT log into Windows -please help
Hi, I need step by step instructions on how to use my recovery console CD in order to fix a problem. Here is my prob: A virus/malware scan I did probably messed up my logon file. The antivrus program either removed or quaranteined the virus/trojan that was attached to my logon file. Because when AVG prompted me to reboot after this scan, It stopped at the welcome screen logon part. I click on my user name (only one there) and it says its logging on...But then it says saving settings and nothing else happens. My machine does not reboot or anything. It just stays at this logon sc...

need basic help setting up a graph
Hello, I am trying to set up a chart of days and times. The X- axis shoul have the days of the week (mon, tues, etc) and the Y - axis should hav various times of the day. I would like to have a line from on specific time on one day to another specific time on the next. So, point would be monday at 10:00 and the next would be tuesday at 1:00 followed by wednesday at 12:00, etc. Any suggestions?? Thanks fo your help Davi -- Message posted from http://www.ExcelForum.com Hi David, See my attachment. Is that the situation you explained? I use formula =IF(B2<0.5,B2+1,B2) in column ...

Newbie Needs Help With Formatting E-Mails
Newbie Needs Help With Formatting E-Mails Hi all. I'm new to this site and would appreciate some formatting help. I created a new Word doc and copied and pasted some graphics and some simple text boxes in to it. The graphics and text boxes were created in Powerpoint by someone else. Looks fine on my screen. Before sending it out via e-mail to an Outlook e-mail group I have, I first sent myself a test message to see what it would look like on the receiving end. It looks a mess! The text boxes are all over the place, as is the text that is not in a box. Also, everything slide over to ...

Move data from column to rows HELP!!!
Hi thanks for taking the time to look at my problem, currently i have column that has thousands of rows of information in it, it looks lik this A 40432 432654 3432 532543 32432 523 53425 532532 532 523 532 111 222 333 666 numbers that go on into mabye the 5000-6000 range what i need to do is have that data moved So it looks like this A | B | C 40432 | 32432 | 532 432654 | 523 | 523 3432 | 52432 | 111 532543 | 532532 | 222 So on and so on, so instead of 1 column with 6000 lines it ...

HELP! CFtpConnection GetFile cause memory leaking, WHY?
I'm trying to use CFtpConnection GetFile to download files from a WS_FTP server. I found if I download 1000 files (Size from 17KB to 25KB ), it can cause about 10M memory leaking. When I commented out the GetFile( ) line, there was no memorry leaking. WHY? Following is part of my source code: try { // Request a connection to Image Server //Use default FTP Port //Use Passive MODE pConnect =3D sess.GetFtpConnection(strISIP,strFtpUser,...

Help, how do I get an exe file with outlook 2002.
is there a way to turn off the GD stupid F#@$ing level one interecept "feature" in outlook? I am so angry with this stupid program@! I need to download an exe file and cannot with this software! Maybe if you washed your mouth out with soap, someone would be willing to help you. This is a technical forum -- not a place for ranting fools. "Sean" <sean@pointblankinc.com> wrote in message news:5a8001c42d8f$52c08b20$a401280a@phx.gbl... > is there a way to turn off the GD stupid F#@$ing level > one interecept "feature" in outlook? > > I am so angr...

Crystal Reports help
We are currently using GP 8.0 and Crystal Reports 10. We will be running most of our reports through Crystal because you can do so much more than with the Report Writer. I am looking for a good reference book for Crystal 10 (this is the first version I have ever used) any suggestions? You can get books on Crystal 10 in any large bookseller. One thing you won't get is how to use Crystal with Great Plains. There was some training materials available. You will want to consult the Great Plains SDK for help on the tables to use. JO wrote: > We are currently using GP 8.0 and Crystal R...

Help with an IIF expression
My database is similar to a checkbook register and contains the following fields: Amount Combo box with the expense categories of Housing, Medical, Personal I have a report that gives me a monthly total for each of these categories and a grand total of all categories for the month. However, I need help in calculating a monthly total for Medical and Personal combined. Please help me with the expression. I want to add a text box to my report to show the results of this expression. Thank you! You can create a hard-coded expression like: =Sum(Abs([ExpCat] IN ("Medi...

Need PO Generator Replenishment Level Help
I=92m confused as to exactly how the Replenishment Level works. On the Purchase Order Generator Item Maintenance screen you can select a Replenishment Level. There are 3 choices: Order Point Qty, Order Up- To Level and Vendor EOQ (if Order Method is set to Independent Site). Online help states: Order Point Qty Enter the quantity this item should be reordered at to maintain your desired stock, including a safety stock quantity. This quantity is used to calculate the quantity to reorder when you print the Purchase Advice Report. For example, if the reorder level is 10, and there are eight in...

HELP: Email body deleted when I receive voting response
Hi All, Whenever I vote using voting buttons, Email body gets deleted when the voting response is sent. So for eg: If I have enabled voting buttons and asked to vote on something from person xyz, the person xyz votes "accept" or "reject" and the email body is deleted when I receive the vote response email. Is there a solution for this? I want the email body to stay when I receive the voting responses. Thanks in advance. ...

Can anyone help with this chart?
I would like to have the following values/labels on the y and x axis of any style of chart: X-axis: July, August, September etc for a full financial year; Y-axis: FB, AP, DR, LA, PC. OR the other way around. Presently the Y-axis simply displays the labels 'Jan Jan Jan Jan Jan' etc. Thank you. Any help would be much appreciated. Check out Jon Peltier's article on creating a vertical category axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "A little stuck" wro...

Restoring Exchange Data
HI. I experienced a crash with my Windows 2000 server which is the Global Catalog and dns server. Anyhow in my infinite wisdom I decided to do soemthing with the exchange server and in the meantime - it blew any active directory info which had replicated to the exchange server. The Exchange server was a part of the domain with AD installed, etc. The exch. server is a running windows 2003 server w/ exch. server 2003. Fast forward...I rebuilt the Windows 2000 server and its backup. I also rebuilt the Exchange Server...not completely knowing how to proceed....I installed w2k3 with all ...