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?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



<BigLar86@gmail.com> wrote in message
news:569fbc7e-9a11-4d29-b6a9-feeb6a304777@w34g2000prm.googlegroups.com...
> 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

On May 20, 1:22*am, "Bob Phillips" <bob....@somewhere.com> wrote:
> Where is the resultant value supposed to go, in a cell to the right, below?
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> <BigLa...@gmail.com> wrote in message
>
> news:569fbc7e-9a11-4d29-b6a9-feeb6a304777@w34g2000prm.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - 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


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

<BigLar86@gmail.com> wrote in message
news:26eb8b90-c1e3-4cca-804e-665fded030d1@a9g2000prl.googlegroups.com...
On May 20, 1:22 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> Where is the resultant value supposed to go, in a cell to the right,
> below?
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> <BigLa...@gmail.com> wrote in message
>
> news:569fbc7e-9a11-4d29-b6a9-feeb6a304777@w34g2000prm.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - 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

On May 20, 7:45*am, "Bob Phillips" <bob....@somewhere.com> wrote:
> 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
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> <BigLa...@gmail.com> wrote in message
>
> news:26eb8b90-c1e3-4cca-804e-665fded030d1@a9g2000prl.googlegroups.com...
> On May 20, 1:22 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
>
>
>
>
> > Where is the resultant value supposed to go, in a cell to the right,
> > below?
>
> > --
> > ---
> > HTH
>
> > Bob
>
> > (there's no email, no snail mail, but somewhere should be gmail in my
> > addy)
>
> > <BigLa...@gmail.com> wrote in message
>
> >news:569fbc7e-9a11-4d29-b6a9-feeb6a304777@w34g2000prm.googlegroups.com...
>
> > > 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- Hide quoted text -
>
> > - 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- Hide quoted text -
>
> - 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.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



<BigLar86@gmail.com> wrote in message
news:c5b59d26-53fa-422a-a193-2b58128f3985@i76g2000hsf.googlegroups.com...
On May 20, 7:45 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> 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
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> <BigLa...@gmail.com> wrote in message
>
> news:26eb8b90-c1e3-4cca-804e-665fded030d1@a9g2000prl.googlegroups.com...
> On May 20, 1:22 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
>
>
>
>
> > Where is the resultant value supposed to go, in a cell to the right,
> > below?
>
> > --
> > ---
> > HTH
>
> > Bob
>
> > (there's no email, no snail mail, but somewhere should be gmail in my
> > addy)
>
> > <BigLa...@gmail.com> wrote in message
>
> >news:569fbc7e-9a11-4d29-b6a9-feeb6a304777@w34g2000prm.googlegroups.com...
>
> > > 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- Hide quoted text -
>
> > - 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- Hide quoted text -
>
> - 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.


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.
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> <BigLa...@gmail.com> wrote in message
>
> news:c5b59d26-53fa-422a-a193-2b58128f3985@i76g2000hsf.googlegroups.com...
> On May 20, 7:45 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
>
>
>
>
> > 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
>
> > --
> > HTH
>
> > Bob
>
> > (there's no email, no snail mail, but somewhere should be gmail in my
> > addy)
>
> > <BigLa...@gmail.com> wrote in message
>
> >news:26eb8b90-c1e3-4cca-804e-665fded030d1@a9g2000prl.googlegroups.com...
> > On May 20, 1:22 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > Where is the resultant value supposed to go, in a cell to the right,
> > > below?
>
> > > --
> > > ---
> > > HTH
>
> > > Bob
>
> > > (there's no email, no snail mail, but somewhere should be gmail in my
> > > addy)
>
> > > <BigLa...@gmail.com> wrote in message
>
> > >news:569fbc7e-9a11-4d29-b6a9-feeb6a304777@w34g2000prm.googlegroups.com....
>
> > > > 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- Hide quoted text -
>
> > > - 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- Hide quoted text -
>
> > - 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 :)- Hide quoted text -
>
> - 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.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



<BigLar86@gmail.com> wrote in message
news:46970ddc-19ac-441a-b4dd-5f2ce19d646f@26g2000hsk.googlegroups.com...
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.
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> <BigLa...@gmail.com> wrote in message
>
> news:c5b59d26-53fa-422a-a193-2b58128f3985@i76g2000hsf.googlegroups.com...
> On May 20, 7:45 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
>
>
>
>
> > 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
>
> > --
> > HTH
>
> > Bob
>
> > (there's no email, no snail mail, but somewhere should be gmail in my
> > addy)
>
> > <BigLa...@gmail.com> wrote in message
>
> >news:26eb8b90-c1e3-4cca-804e-665fded030d1@a9g2000prl.googlegroups.com...
> > On May 20, 1:22 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > Where is the resultant value supposed to go, in a cell to the right,
> > > below?
>
> > > --
> > > ---
> > > HTH
>
> > > Bob
>
> > > (there's no email, no snail mail, but somewhere should be gmail in my
> > > addy)
>
> > > <BigLa...@gmail.com> wrote in message
>
> > >news:569fbc7e-9a11-4d29-b6a9-feeb6a304777@w34g2000prm.googlegroups.com...
>
> > > > 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- Hide quoted text -
>
> > > - 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- Hide quoted text -
>
> > - 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 :)- Hide quoted text -
>
> - Show quoted text -

Thanks again, it was the ser

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.
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> <BigLa...@gmail.com> wrote in message
>
> news:46970ddc-19ac-441a-b4dd-5f2ce19d646f@26g2000hsk.googlegroups.com...
> 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.
>
> > --
> > ---
> > HTH
>
> > Bob
>
> > (there's no email, no snail mail, but somewhere should be gmail in my
> > addy)
>
> > <BigLa...@gmail.com> wrote in message
>
> >news:c5b59d26-53fa-422a-a193-2b58128f3985@i76g2000hsf.googlegroups.com...
> > On May 20, 7:45 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > 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
>
> > > --
> > > HTH
>
> > > Bob
>
> > > (there's no email, no snail mail, but somewhere should be gmail in my
> > > addy)
>
> > > <BigLa...@gmail.com> wrote in message
>
> > >news:26eb8b90-c1e3-4cca-804e-665fded030d1@a9g2000prl.googlegroups.com....
> > > On May 20, 1:22 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > > Where is the resultant value supposed to go, in a cell to the right,
> > > > below?
>
> > > > --
> > > > ---
> > > > HTH
>
> > > > Bob
>
> > > > (there's no email, no snail mail, but somewhere should be gmail in my
> > > > addy)
>
> > > > <BigLa...@gmail.com> wrote in message
>
> > > >news:569fbc7e-9a11-4d29-b6a9-feeb6a304777@w34g2000prm.googlegroups.com...
>
> > > > > 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- Hide quoted text -
>
> > > > - 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- Hide quoted text -
>
> > > - 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 :)- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks again, it was the ser- Hide quoted text -
>
> - 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




--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



<BigLar86@gmail.com> wrote in message
news:6bdb1065-0d7f-4ea6-b8c5-a644e4b1407c@z72g2000hsb.googlegroups.com...
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.
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> <BigLa...@gmail.com> wrote in message
>
> news:46970ddc-19ac-441a-b4dd-5f2ce19d646f@26g2000hsk.googlegroups.com...
> 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.
>
> > --
> > ---
> > HTH
>
> > Bob
>
> > (there's no email, no snail mail, but somewhere should be gmail in my
> > addy)
>
> > <BigLa...@gmail.com> wrote in message
>
> >news:c5b59d26-53fa-422a-a193-2b58128f3985@i76g2000hsf.googlegroups.com...
> > On May 20, 7:45 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > 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
>
> > > --
> > > HTH
>
> > > Bob
>
> > > (there's no email, no snail mail, but somewhere should be gmail in my
> > > addy)
>
> > > <BigLa...@gmail.com> wrote in message
>
> > >news:26eb8b90-c1e3-4cca-804e-665fded030d1@a9g2000prl.googlegroups.com...
> > > On May 20, 1:22 am, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > > Where is the resultant value supposed to go, in a cell to the right,
> > > > below?
>
> > > > --
> > > > ---
> > > > HTH
>
> > > > Bob
>
> > > > (there's no email, no snail mail, but somewhere should be gmail in
> > > > my
> > > > addy)
>
> > > > <BigLa...@gmail.com> wrote in message
>
> > > >news:569fbc7e-9a11-4d29-b6a9-feeb6a304777@w34g2000prm.googlegroups.com...
>
> > > > > 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- Hide quoted text -
>
> > > > - 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- Hide quoted text -
>
> > > - 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 :)- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks again, it was the ser- Hide quoted text -
>
> - 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.