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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top