Increment Value On Basis of Count with Button Click |Excel|VBA|

[jackbhai]

I want the Value should be Auto Printed Number of Times When Count is Given
[My Image 1][1]: https://tinyurl.com/yxlcsypz
If you see my image there is Count and Value as some Data like 'DEMO' When i enter count like 5 then the Data in Value = Demo should be printed that many times as Count is mentioned Under Column => H
Like Below Output Image [My Image 2][1]: https://tinyurl.com/y4lajcep
I can Drag the cell upto particular Count... this may work for me But when the count is 5 or 10 ... what if the Count is 100 or 200 or more We have to drag the cell upto 100 count and so on
i want to auto increment without Dragging the cell upto the Particular count
I have tried this one But not Working ... Suggest me How do i achieve this


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub Increment()

Dim startValue
startValue = Cells(15, 5).Value

For i = 1 To 10 Step 1
Cells(i, 5).Value = startValue
startValue = startValue + 1
Next i
End Sub</code>

 

[Leith Ross]

Hello jackbhai,

This is easily accomplished using the Worksheet Change event.

Worksheet Event Code

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim RngBeg As Range
    Dim RngEnd As Range
    
        If Target.Cells.Count > 1 Then Exit Sub
        If Intersect(Target, Range("C3,F3")) Is Nothing Then Exit Sub
    
        Set RngBeg = Range("H3")
        Set RngEnd = Cells(Rows.Count, "H").End(xlUp)
        If RngEnd.Row >= RngBeg.Row Then Range(RngBeg, RngEnd).ClearContents
        
        Application.EnableEvents = False
        
        On Error Resume Next
            RngBeg.Resize(Range("C3"), 1).Value = Range("F3").Value
        On Error GoTo 0
        
        Application.EnableEvents = True
        
End Sub

How to Paste a Macro into a Worksheet module

• Copy the macro code using Ctrl+C.
• Right Click the Sheet Tab at the bottom of the worksheet.
• Click View Code
• Paste the macro into the module with Ctrl+V.
• Save the macro using Ctrl+S