Hi,
I have a worksheet where in column 6 or E, I want when the user enter a date to automatically be replaced by a Quarter.see the image enclosed. I know that the formula to calculate the quarter is as follow but how to integrate the quarter into a cell after the entering a date into the same cell?
="Q"&INT((MONTH(E11)+2)/3)
Your help would be appreciated.
Thanks in advance.
Chuck
Is there a way to replace a date by inserting a Quarter
-
- 3StarLounger
- Posts: 372
- Joined: 17 Dec 2013, 00:07
Is there a way to replace a date by inserting a Quarter
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Is there a way to replace a date by inserting a Quarter
Try this:
Right click on the sheet tab of the worksheet that contains the data.
Choose View Code
Paste the code below onto the module and give it a try by typing a date into cell E11...
Right click on the sheet tab of the worksheet that contains the data.
Choose View Code
Paste the code below onto the module and give it a try by typing a date into cell E11...
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("E11")) Is Nothing Then
If IsDate(Target.Value) Then
Target.Value = "Q" & CInt((Month(Target.Value) + 2) / 3)
End If
End If
Application.EnableEvents = True
End Sub
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 372
- Joined: 17 Dec 2013, 00:07
Re: Is there a way to replace a date by inserting a Quarter
Hi Rudi,
Thanks for your prompt reply but what if the row changes since there will be many rows. Would you change the following:
by
I made that amendment and it worked.
Chuck
Thanks for your prompt reply but what if the row changes since there will be many rows. Would you change the following:
Code: Select all
If Not Intersect(Target, Range("E11")) Is Nothing Then
Code: Select all
If Not Intersect(Target, Range("E" & Target.Row)) Is Nothing Then
![ThankYou :thankyou:](./images/smilies/thankyou.gif)
Chuck
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Is there a way to replace a date by inserting a Quarter
Well done...
(You are becoming a skilled programmer)
![ThumbUp :thumbup:](./images/smilies/thumbup.gif)
(You are becoming a skilled programmer)
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78887
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is there a way to replace a date by inserting a Quarter
If there is a chance that you enter a date in multiple cells at once (for example by confirming a value with Ctrl+Enter), the following version will handle that:
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Not Intersect(Target, Range("E:E")) Is Nothing Then
Application.EnableEvents = False
For Each cel In Intersect(Target, Range("E:E"))
If IsDate(cel.Value) Then
cel.Value = "Q" & (Month(cel.Value) + 2) \ 3
End If
Next cel
Application.EnableEvents = True
End If
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 372
- Joined: 17 Dec 2013, 00:07
Re: Is there a way to replace a date by inserting a Quarter
Thanks Hans,
The same column but each time in a different row.
Is it possible to have the intersect start at a particular row and column? for instance see the following:
Just wondering whether this would work? I will give this a try and will let you know.
Chuck
The same column but each time in a different row.
Is it possible to have the intersect start at a particular row and column? for instance see the following:
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Not Intersect(Target, Range("E" & Target.Row & ":" & "E")) Is Nothing Then
Application.EnableEvents = False
For Each cel In Intersect(Target, Range("E" & Target.Row & ":" & "E"))
If IsDate(cel.Value) Then
cel.Value = "Q" & (Month(cel.Value) + 2) \ 3
End If
Next cel
Application.EnableEvents = True
End If
End Sub
![Cheers :cheers:](./images/smilies/cheers.gif)
Chuck
Last edited by chamdan on 19 Mar 2016, 20:40, edited 1 time in total.
-
- Administrator
- Posts: 78887
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is there a way to replace a date by inserting a Quarter
If you want to start at row 5, for example, you'd have to use Range("E5:E" & Rows.Count):
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
If Not Intersect(Target, Range("E5:E" & Rows.Count)) Is Nothing Then
Application.EnableEvents = False
For Each cel In Intersect(Target, Range("E5:E" & Rows.Count))
If IsDate(cel.Value) Then
cel.Value = "Q" & (Month(cel.Value) + 2) \ 3
End If
Next cel
Application.EnableEvents = True
End If
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 372
- Joined: 17 Dec 2013, 00:07
Re: Is there a way to replace a date by inserting a Quarter
![ThankYou :thankyou:](./images/smilies/thankyou.gif)
![Cheers :cheers:](./images/smilies/cheers.gif)
Chuck