Delete cell entry and default contents are restored, for multiplerows
 

I currently have a macro that allows the content in B1 to be reflected
in A1 until I enter a different number in A1. If I choose to delete
the entry I made in A1, by default the B1 content will once again
appear in A1. My macro is as follows:

Private Sub Worksheet_Change(ByVal TargetCell As Range)
If Not Intersect(TargetCell, Range("A1")) Is Nothing Then
If TargetCell = "" Then
TargetCell = "=B1"
End If
End If
End Sub

Here is my problem. I have approximately 3000 rows, all of which need
to perform in the same way as stated above. Instead of creating a
macro for each row, I need a single macro that will perform the
assignment. Can anyone offer a solution? I thank you in advance.

Delete cell entry and default contents are restored, for multiple rows
 

Although I have never seen a worksheet change event using TargetCell instead
of just Target.....

Works on just one cell at a time:

Private Sub Worksheet_Change(ByVal TargetCell As Range)
If TargetCell.Cells.Count 1 Then Exit Sub
If Not Intersect(TargetCell, Range("A:A")) Is Nothing Then
If TargetCell.Value = "" Then
'Choose one of these two - the first puts values, the second puts in a
formula
TargetCell.Value = TargetCell.Offset(0,1).Value
TargetCell.Formula = "=" & TargetCell.Offset(0,1).Adress
End If
End If
End Sub

HTH,
Bernie
MS Excel MVP


wrote in message
...
I currently have a macro that allows the content in B1 to be reflected
in A1 until I enter a different number in A1. If I choose to delete
the entry I made in A1, by default the B1 content will once again
appear in A1. My macro is as follows:

Private Sub Worksheet_Change(ByVal TargetCell As Range)
If Not Intersect(TargetCell, Range("A1")) Is Nothing Then
If TargetCell = "" Then
TargetCell = "=B1"
End If
End If
End Sub

Here is my problem. I have approximately 3000 rows, all of which need
to perform in the same way as stated above. Instead of creating a
macro for each row, I need a single macro that will perform the
assignment. Can anyone offer a solution? I thank you in advance.

Delete cell entry and default contents are restored, for multiple rows
 

I've added code to handle multiple cell entries like clearing a range or
using Ctrl-Enter. Also, it's a good idea to turn off events when an event
handler changes the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Not Intersect(Target, Range("A:A")) Is Nothing Then
For Each Cell In Intersect(Target, Range("A:A"))
If Cell.Value = "" Then
Application.EnableEvents = False
Cell.FormulaR1C1 = "=RC[1]"
Application.EnableEvents = True
End If
Next
End If
End Sub


--
Jim
wrote in message
...
|I currently have a macro that allows the content in B1 to be reflected
| in A1 until I enter a different number in A1. If I choose to delete
| the entry I made in A1, by default the B1 content will once again
| appear in A1. My macro is as follows:
|
| Private Sub Worksheet_Change(ByVal TargetCell As Range)
| If Not Intersect(TargetCell, Range("A1")) Is Nothing Then
| If TargetCell = "" Then
| TargetCell = "=B1"
| End If
| End If
| End Sub
|
| Here is my problem. I have approximately 3000 rows, all of which need
| to perform in the same way as stated above. Instead of creating a
| macro for each row, I need a single macro that will perform the
| assignment. Can anyone offer a solution? I thank you in advance.

Delete cell entry and default contents are restored, for multiplerows
 

Thanks guys for your help.

M