CODEWITHSUNDEEP
vbaexcel
Hydes Yase
Hydes Yase

Reputation: 65

Excel VBA: Add data to cell after last used row

enter image description here The pic shows what happens with my code.

I have a user form and I add the labels of the user form into the selected worksheet. And this is what I tried. Now the problem is why is it that there is one cell not on the same row as the others? 

Dim c As Control
For Each c In Me.Controls
    If TypeName(c) = "Label" Then
        With ActiveSheet
            i = i + 1
            Dim lastRow As Long
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

            If c <> "Chapter" Then
                .Range(Cells(1, 1), Cells(1, i)).Name = "Chapter1"
                .Range("Chapter1").Merge
                .Range("Chapter1").Value = "Chapter 1"
                .Range("Chapter1").HorizontalAlignment = xlCenter

                .Cells(lastRow, i).Value = c.Caption
            End If
        End With
    End If
Next

Upvotes: 1

Views: 5075

Answers (1)

trincot
trincot

Reputation: 351218

The problem is that the first time you do .Cells(.Rows.Count, 1).End(xlUp).Row there will not be anything yet in A2, so lastRow will be 1. But once you put the value "No." in that cell the next time you execute that code (with i being 2), A2 will be filled, so now .Cells(.Rows.Count, 1).End(xlUp).Row will return 2, giving you the effect you get: all other values end up one row lower.

There are several way to solve this, but here is one way. Add + IIf(i = 1, 1, 0) the assignment of lastRow:

lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + IIf(i = 1, 1, 0)

Upvotes: 1

Related Questions