CODEWITHSUNDEEP
vbaexcel
Ras Rass
Ras Rass

Reputation: 225

inserting the same value continuously in vba-excel

I'm newbie in vba.. i don't know how to loop the same value from the existing field of sheet. I had search on in but I didn't find what i'm searching for.

This is my case .For example. i have a field like this :

year     value
2012     100
2013     300
2014     400

i have new data 2015 that must be insert for 5 times and the result must be like this

year     value
2012     100
2013     300
2014     400
2015     900
2015     900
2015     900
2015     900
2015     900

I did my code like this :

Private Sub AddPostClick_Click()
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("DATA")

    lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With ws

        If CheckBox1.Value = False Then
                For lRow = 1 To 5
                    .Cells(lRow + 1, 1).Value = tYear.Value
                    .Cells(lRow + 1, 2).Value = txtValue.Value
                Next lRow
        End If

End With
End Sub

my code will be replace the previous rows, not insert it continuously from the last existing row. This is my problem.

Upvotes: 2

Views: 373

Answers (1)

Amen Jlili
Amen Jlili

Reputation: 1944

You're overwriting lrow. Declare a new long. Call i and use it as counter.

        Dim i as long
        For i = Lrow + 1 To lrow + 1 + 5 
                    .Cells(i, 1).Value = tYear.Value
                    .Cells(i, 2).Value = txtValue.Value
                Next lRow

Upvotes: 1

Related Questions