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.

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


<(E-Mail Removed)> wrote in message
news:44513b74-9c45-4418-ace6-(E-Mail Removed)...
>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.

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
<(E-Mail Removed)> wrote in message
news:44513b74-9c45-4418-ace6-(E-Mail Removed)...
|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.

Thanks guys for your help.

M