VBA coding for the question

vishwajeet_chakravorty

Board Regular
Joined
Mar 8, 2010
Messages
120
Dear Sir, Suppose I write any number (Example 100) in Cell A1, the cells A2 to A12 should be filled with the same number automatically. And again in cell A4 I write any other number, the cell from A5 to A12 should be filled with the same number automatically. How to write a VBA code for this thing to happen?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
You could put this in your sheet code module....or if you want that on every sheet, convert to Workbook_SheetChange

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rw As Long
Dim Col As Long
Dim i As Integer
    Rw = Target.Row
    Col = Target.Column
    
    If Rw >= 12 Then Exit Sub
    
    For i = Rw + 1 To 12
        Cells(i, Col).Value = Cells(Rw, Col).Value
    Next i
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try this: right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row < 12 Then
    Application.EnableEvents = False
    Range(Cells(Target.Row + 1, 1), Cells(12, 1)).Value = Target.Value
    Application.EnableEvents = True
End If
End Sub
 

krausr79

Board Regular
Joined
Feb 12, 2012
Messages
209
for x=2 to 12
cells(x,1).vale=cells(1,1).value
next

for x=5 to 12
cells(x,4).vale=cells(1,4).value
next
 

vishwajeet_chakravorty

Board Regular
Joined
Mar 8, 2010
Messages
120
Try this: right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row < 12 Then
    Application.EnableEvents = False
    Range(Cells(Target.Row + 1, 1), Cells(12, 1)).Value = Target.Value
    Application.EnableEvents = True
End If
End Sub
Dear sir It is working in cell A1 to A12. Now my questions are
1)If I want to repeat the same action from C1 to C12 what will be the changes in the existing code?
2) If I want to do, not from A1 to A12 but from A4 to A15 them what will be the changes in the existing code? Actually I want to see the differences in coding and how it works.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
This will work in any column for rows up to 12

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 12 Then
    Application.EnableEvents = False
    Range(Cells(Target.Row + 1, Target.Column), Cells(12, Target.Column)).Value = Target.Value
    Application.EnableEvents = True
End If
End Sub
 

vishwajeet_chakravorty

Board Regular
Joined
Mar 8, 2010
Messages
120
This will work in any column for rows up to 12

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 12 Then
    Application.EnableEvents = False
    Range(Cells(Target.Row + 1, Target.Column), Cells(12, Target.Column)).Value = Target.Value
    Application.EnableEvents = True
End If
End Sub
Dear Sir, If I do not want in all the columns. I want to do it in few columns like A1 to A12, C1 to C12, E1 to E12 and H1 to H12, then how should I change this code?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 12 Then
    Select Case Target.Column
        Case 1, 3, 5, 8
            Application.EnableEvents = False
            Range(Cells(Target.Row + 1, Target.Column), Cells(12, Target.Column)).Value = Target.Value
            Application.EnableEvents = True
    End Select
End If
End Sub
 

vishwajeet_chakravorty

Board Regular
Joined
Mar 8, 2010
Messages
120
Try

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 12 Then
    Select Case Target.Column
        Case 1, 3, 5, 8
            Application.EnableEvents = False
            Range(Cells(Target.Row + 1, Target.Column), Cells(12, Target.Column)).Value = Target.Value
            Application.EnableEvents = True
    End Select
End If
End Sub
Dear Sir,
Now one more question If I want to do the same from A5 to A15, C5 to C15 and F5 to F15, Then want will be the changes. Thanks for the quick reply.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
You should be able to see a pattern emerging:

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 15 Then
    Select Case Target.Column
        Case 1, 3, 6
            Application.EnableEvents = False
            Range(Cells(Target.Row + 1, Target.Column), Cells(15, Target.Column)).Value = Target.Value
            Application.EnableEvents = True
    End Select
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,011
Messages
5,466,014
Members
406,461
Latest member
Garrus

This Week's Hot Topics

Top