Macro-writing problems

[Richard]

Hi All,

I've got two problems writing a macro. I frequently to record the date
when I receive phone calls. Instead of typing yyyy.mm.dd, I'd like to
type, say, ctrl-/ and dd.

I like to use the macro as follows:

1. Whenever a new month occurs, I'll store the year and month in G2.
For this month, e.g., I'd store "2007.01." in G2.
2. Whenever I want to record a date sometime in this month, I'd click
the target cell and press Ctrl-/, and then dd to append today's day.

I'd like the macro to work as follows:

1. Store the current cell in, say, G3.
2. Make G2 the current cell.
3. Store G2's content by simulating Ctrl-C
4. Make the cell we started with the current cell
5. Paste the saved content by storing it in the current cell

The following script is my humble attempt at doing this. Is there an
easy way to achieve my scripting goal?

Sub CopyG2()
'
' CopyG2 Macro
' Macro recorded 1/20/2007 by RLMuller
'

'
Range("G2").Select
Selection.Copy
Range("D37").Select
ActiveSheet.Paste
Range("D37").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2007.01."
Range("E37").Select
End Sub

Thanks in advance,
Richard

 

[Dave Peterson]

Instead of using a macro, you could use excel's builtin shortcut key:

ctrl-semicolon (ctrl- will give you the date.

ctrl-colon (ctrl- will give you the date.

And you can format the cells anyway you want (yyyy.mm.dd) before or after you
enter the date.

 

[CLR]

Maybe this........each time a value is typed in to a cell in column A, this
macro will automatically put the date and time in the corresponding row in
column B......

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myrange
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
myrange = Target.Value
Target.Offset(0, 1).Value = Date & "," & Time
End If
enditall:
Application.EnableEvents = True
End Sub


hth
Vaya con Dios,
Chuck, CABGx3

 

[Richard]

Hi Dave and CLR,

Thank you both for great solutions. I don't use Excel much, but I
found it more useful the MS Word's tables for this particular purpose.
Thus, I'm really grateful for much better solutions than my
hand-crafted macro idea.

Best wishes,
Richard

 

[Richard]

Maybe this........each time a value is typed in to a cell in column A, this
macro will automatically put the date and time in the corresponding row in
column B......

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myrange
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
myrange = Target.Value
Target.Offset(0, 1).Value = Date & "," & Time
End If
enditall:
Application.EnableEvents = True
End Sub


hth
Vaya con Dios,
Chuck, CABGx3

Hi Check,

I opted to try your solution.

I ran into two problems:

1. Excel complained about unsigned macros, so I dropped the security
level to Low. (I'm running AVG, which in my experience is a great
anti-virus utility.

2. No cell gets populated with a date when I enter data in col. A of a
new row. I modified your script to target col. E rather than D
(correctly I hope) and omit the time. I see the script shown below
when I open Tools | Macro | VBEditor. I noted that the local variable
"myrange" is assigned a value which apparently is never accessed.
Could that be a cause of my problem?

Any ideas?

Again, thanks for your help.

Regards,
Richard

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myrange
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
myrange = Target.Value
Target.Offset(0, 4).Value = Date
End If
enditall:
Application.EnableEvents = True
End Sub

 

[Richard]

Hi Chuck,

I opted to try your solution.

I ran into two problems:

1. Excel complained about unsigned macros, so I dropped the security
level to Low. (I'm running AVG, which in my experience is a great
anti-virus utility.

2. No cell gets populated with a date when I enter data in col. A of a
new row. I modified your script to target col. E rather than B
(correctly I hope) and omit the time. I see the script shown below
when I open Tools | Macro | VBEditor. I noted that the local variable
"myrange" is assigned a value which apparently is never accessed.
Could that be a cause of my problem?

Any ideas?

Again, thanks for your help.

Regards,
Richard

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myrange
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
myrange = Target.Value
Target.Offset(0, 4).Value = Date
End If
enditall:
Application.EnableEvents = True
End Sub

 

[CLR]

Hi Richard........

This one will put a date in E for a value in D, note the difference from my
first posting which put date in B for entry in A

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myrange
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 4 Then 'the 4 indicated column D
myrange = Target.Value
Target.Offset(0, 1).Value = Date ' the 1 indicates the next column
to the right
End If
enditall:
Application.EnableEvents = True
End Sub

Vaya con Dios,
Chuck, CABGx3