 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Fiscal Year function
Hi new to the forum - hope this is correct format Half my work deals with the calendar year 1/1-12/21 and half deals with FY 10/1 - 9/30 I am trying to build a function that i could apply to a worksheet that would search out all cells formated as date and change date to FY date set. I am new to VBA but not to excel - this is what i use currently =IF(MONTH(cell)<=9,YEAR(cell),YEAR(cell)+1 any ideas? BigLar Where is the resultant value supposed to go, in a cell to the right, below?  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi new to the forum - hope this is correct format > [quoted text clipped - 12 lines] > > BigLar > Where is the resultant value supposed to go, in a cell to the right, below? > [quoted text clipped - 28 lines] > > - Show quoted text - Currently cell to right - best of all worlds would like to insert cell to right, run, delete first cell so my sheet structure was not altered. Lar Public Sub ProcessData() Dim cell As Range For Each cell In ActiveSheet.UsedRange.Cells If Month(cell.Value) <= 9 Then cell.Value = DateSerial(Year(cell.Value) + 1, Month(cell.Value), Day(cell.Value)) End If Next cell End Sub SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Where is the resultant value supposed to go, in a cell to the right, > below? [quoted text clipped - 30 lines] > > - Show quoted text - Currently cell to right - best of all worlds would like to insert cell to right, run, delete first cell so my sheet structure was not altered. Lar > Public Sub ProcessData() > Dim cell As Range [quoted text clipped - 61 lines] > > - Show quoted text - Thank you! I am getting an 'expected arguement' error - but i am not asking for clarification. I appreciate that you given me and want to try and take it apart to learn from. Thanks :) It is probably that the DateSerial line has got wrapped into two lines in the post. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Public Sub ProcessData() > Dim cell As Range [quoted text clipped - 62 lines] > > - Show quoted text - Thank you! I am getting an 'expected arguement' error - but i am not asking for clarification. I appreciate that you given me and want to try and take it apart to learn from. Thanks :) it was the DateSerial line - took me about 45 to figure it out but i got it. thank you thank you. I am working on error handling, for fields with text and blank fields - if you would be so kind as to point me in the right direction I would like to give it a try. > It is probably that the DateSerial line has got wrapped into two lines in > the post. [quoted text clipped - 85 lines] > > - Show quoted text - Thanks again, it was the ser I will try, but I need a bit more detail as to what you are alluding to. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) it was the DateSerial line - took me about 45 to figure it out but i got it. thank you thank you. I am working on error handling, for fields with text and blank fields - if you would be so kind as to point me in the right direction I would like to give it a try. On May 21, 7:08 am, "Bob Phillips" <bob....@somewhere.com> wrote: > It is probably that the DateSerial line has got wrapped into two lines in > the post. [quoted text clipped - 88 lines] > > - Show quoted text - Thanks again, it was the ser > I will try, but I need a bit more detail as to what you are alluding to. > [quoted text clipped - 114 lines] > > - Show quoted text - Sorry about that - it works great for all cells with date - but i have a blank cell it formats as date and puts the 1900 date in there. Also if a cell has text it throws an error of data type mismatch. Any help would be appreciated. lar. Public Sub ProcessData() Dim cell As Range For Each cell In ActiveSheet.UsedRange.Cells If cell.Value <> "" Then If Month(cell.Value) <= 9 Then cell.Value = DateSerial(Year(cell.Value) + 1, _ Month(cell.Value), _ Day(cell.Value)) End If End If Next cell End Sub Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) On May 22, 2:46 am, "Bob Phillips" <bob....@somewhere.com> wrote: > I will try, but I need a bit more detail as to what you are alluding to. > [quoted text clipped - 115 lines] > > - Show quoted text - Sorry about that - it works great for all cells with date - but i have a blank cell it formats as date and puts the 1900 date in there. Also if a cell has text it throws an error of data type mismatch. Any help would be appreciated. lar. > Public Sub ProcessData() > Dim cell As Range [quoted text clipped - 153 lines] > > - Show quoted text - Sweet - thank you very much! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.