Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extra added to a script
In the A Column I would like the date to be entered as well?
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .[C1] = "" Then Union([B162], [E162]).Copy .[C1] Else Union([B162], [E162]).Copy .Range("C" & Rows.Count). _ End(xlUp)(2) End If [B162].Select End With End If End Sub Thanks in advance.........Bob Vance |
#2
|
|||
|
|||
Using [C1] is slower than using Range("C1") and for me, I find it more difficult
to read. But something like this might work ok: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim NextRow As Long If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .Range("C1").Value = "" Then NextRow = 1 Else NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row End If Me.Range("b162,E162").Copy _ Destination:=.Cells(NextRow, "C") With .Cells(NextRow, "A") .Value = Now 'date ??? .NumberFormat = "mm/dd/yyyy hh:mm:ss" 'mm/dd/yyyy ???? End With End With Me.Range("B162").Select End If End Sub Bob wrote: In the A Column I would like the date to be entered as well? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .[C1] = "" Then Union([B162], [E162]).Copy .[C1] Else Union([B162], [E162]).Copy .Range("C" & Rows.Count). _ End(xlUp)(2) End If [B162].Select End With End If End Sub Thanks in advance.........Bob Vance -- Dave Peterson |
#3
|
|||
|
|||
Brilliant Thank You Dave :)
"Dave Peterson" wrote in message ... Using [C1] is slower than using Range("C1") and for me, I find it more difficult to read. But something like this might work ok: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim NextRow As Long If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .Range("C1").Value = "" Then NextRow = 1 Else NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row End If Me.Range("b162,E162").Copy _ Destination:=.Cells(NextRow, "C") With .Cells(NextRow, "A") .Value = Now 'date ??? .NumberFormat = "mm/dd/yyyy hh:mm:ss" 'mm/dd/yyyy ???? End With End With Me.Range("B162").Select End If End Sub Bob wrote: In the A Column I would like the date to be entered as well? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Address = "$E$162" Then With Sheets("ChequesOut") If .[C1] = "" Then Union([B162], [E162]).Copy .[C1] Else Union([B162], [E162]).Copy .Range("C" & Rows.Count). _ End(xlUp)(2) End If [B162].Select End With End If End Sub Thanks in advance.........Bob Vance -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OsCommerce - Easy Populate Script - CSV/TXT Conversion Problem. | Excel Discussion (Misc queries) | |||
VBA script help..Please !!!! | Excel Discussion (Misc queries) | |||
Using a VB Script for barcodes - new to this | Excel Discussion (Misc queries) | |||
How to remove an extra line added by excel when pasting into notep | Excel Discussion (Misc queries) | |||
Extra line space | Excel Discussion (Misc queries) |