A few things please

  • Thread starter Thread starter That's Confidential
  • Start date Start date
T

That's Confidential

I am looking for help with a few things please:-

In column B, I have a customer's account number. All account numbers begin
with 12345678, so when I click in a cell in Column B, I would like these
numbers to appear automatically in the column, and then I can add on the
additional numbers of the account.

Once I have entered a 19 digit number in total (including 12345678) in a
cell in column B, I would like the date to automatically appear in column C,
ie the date on which the account number was typed in.

Now in column D, I would like the date to automatically appear, 14 days
later than the date in column B. So, if the date in B is 14/02/2004, then I
would like 28/02/2004 to automatically appear in column C

Thanks in advance
 
Hi
most of the things you're planning to do require VBA
(event procedures):

1. Account number: First format column B as text as you
plan to enter more than 15 digits after this put the
following code in your worksheet module (not in a standard
module):
Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
If Intersect(Range("B1:B100"), Target) Is Nothing Then
Exit Sub
With Target
If .Value = "" Then
Application.EnableEvents = False
.Value = "12345678"
SendKeys "{F2}"
End If
End With

errhandler:
Application.EnableEvents = True
End Sub


2. Date/Timestamp: Use the following code in your
worksheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("B1:B100"), Target) Is Nothing Then
Exit Sub
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Offset(0, 1).NumberFormat = "MM-DD-YYYY"
.Offset(0, 1).Value = Now
Else
.Offset(0, 1).ClearContents
End If
End With

errhandler:
Application.EnableEvents = True
End Sub

3. 14 days later: enter the following formula in D1
=IF(C1<>"",C1+14,"")
and format this column as date. Copy this formula down for
all rows.
Another option would be to change second VBA procedure:#
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("B1:B100"), Target) Is Nothing Then
Exit Sub
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Offset(0, 1).NumberFormat = "MM-DD-YYYY"
.Offset(0, 1).Value = Now
.Offset(0, 2).NumberFormat = "MM-DD-YYYY"
.Offset(0, 2).Value = Now+14
Else
.Offset(0, 1).ClearContents
.Offset(0, 2).ClearContents
End If
End With

errhandler:
Application.EnableEvents = True
End Sub
 
Excel dates are stored as the number of days since 1900, so in in D1 put
the formula
=IF(ISNUMBER(C1),C1+14,"")

There is no function that would statically compute the date that
something happened. You will have to use a VBA macro tied to the
worksheet change event for column C. If you don't mind a little
handwork, you could put
=IF(isblank(B1),"",TODAY())
with the appropriate date format on the cell. Once you enter the
account number (which calculates the date), then copy and Edit|Paste
Special|Values the date to freeze it.

Note that Excel only supports 15 digit numeric accuracty. If the
account number is all digits, you could use the custom format
1234567800000000000 which would only store the 11 digits following
12345678 (but would still display the leading 12345678), or enter the
number as text. You could possibly use an event tied macro using
SendKeys to pretype 123456778, but the custom numeric format seems more
straightforward.

Jerry
 
It's OK!

I have it now so that the dates are automatically entered into the fields as
requested!

Thanks all (Jerry adn Frank!)
 
Back
Top