Fiscal Year function

[BigLar86]

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

 

[Bob Phillips]

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]

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)










- 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

 

[Bob Phillips]

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)

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)










- 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

 

[BigLar86]

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)





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

 

[Bob Phillips]

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)

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)





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

 

[BigLar86]

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.

--
---
HTH

Bob

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










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

 

[Bob Phillips]

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)



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.

--
---
HTH

Bob

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










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

 

[BigLar86]

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)


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.







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.

 

[Bob Phillips]

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)

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)


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.







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.

 

[BigLar86]

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)








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.- Hide quoted text -

- Show quoted text -

Sweet - thank you very much!