Excel Name Macro

Posted by Manju on September 09, 2000 9:23 AM

Hi,

I want a solution of the my problem with macro. Its very urgent.

If I type a name in the cell Al, is should be repeated in A3, A6,
A9, A9, A12, A15 etc. This should happen if the name has some
specified value. For example if the name in A1 contains "Manju",
I want it to get it repeated in the respective cells mentioned
above. All this I want to do by using a Macro.

In the same way if the name is typed in C1 then it should get
repeated in C3, C6, C9 and so on. This should happen with same
macro.

Please Help........................

Posted by Ivan Moala on September 09, 0100 4:41 PM

Try putting this into your worksheets code module
avail by right click the sheet tab name and selecting
view code;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim x As Integer
If Target.Address = "$A$1" Or Target.Address = "$C$1" Then
If Target.Areas.Count &LT; 2 Then
For x = 2 To 14 Step 3
Target.Offset(x, 0) = Target.Text
Next
End If
End If

End Sub

HTH

Ivan

Posted by Celia on September 09, 0100 5:42 PM

If you want this to be done only when "Manju" is entered in A1 or in C1, then :-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim x As Integer, theName As String
theName = "Manju"
If Target.Address = "$A$1" Or Target.Address = "$C$1" Then
If Target.Areas.Count &LT; 2 And Target.Value = theName Then
For x = 2 To 14 Step 3
Target.Offset(x, 0) = Target.Text
Next
Else
For x = 2 To 14 Step 3
Target.Offset(x, 0).ClearContents
Next
End If
End If
End Sub

Celia