PDA

View Full Version : Change cells value base on other cell value by worksheet event



sharad.sony
11-02-2016, 09:45 PM
I have an excel sheet and cell B2 I declare as integer.

I wish that when I enter in cell b2 as 3, value of cells b3 to d3 change automatically as Item-1, Item-2, Item-3.

And when I enter in cell b2 as 5, value of cells b3 to f3 change automatically as Item-1, Item-2, Item-3, Item-4, Item-5.

Please help.

sharad.sony
11-02-2016, 09:51 PM
I have tried this

Private Sub item ()
Dim n as Integer
Range("B2").value = n
For i = 1 to n
Range (cells(3,1+i) = "Item-"&n
Next i
End Sub


But the same is not working.

mana
11-03-2016, 12:05 AM
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

With Range("B2")
If Intersect(Target, .Cells) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range(.Offset(1), .Offset(1).End(xlToRight)).ClearContents
If IsNumeric(.Value) Then
For i = 1 To CInt(.Value)
.Offset(1, i - 1) = "Item-" & i
Next
End If
End With
Application.EnableEvents = True

End Sub