issue with VBA OFFSET

[kelly mort]

Hello everybody,
I have this code I am trying to use to send data to my worksheet. But it is not doing what I want it to do for me. When I click the add button, it sends the data to the last row in excel. Meanwhile I wants it to send the data to the last empty row or cell. I need help to resolve this. Thanks
Kelly

Private Sub CmdAdd_Click()
Dim Drng As Range
    Set Drng = Sheet1.Range("B6")
    Drng.End(xlDown).Offset(0, 0).Value = Drng.End(xlDown).Offset(-1, 0).Value + 1
    Drng.End(xlDown).Offset(0, 1).Value = Me.Rw2.Value
    Drng.End(xlDown).Offset(0, 2).Value = Me.Rw3.Value
    Drng.End(xlDown).Offset(0, 3).Value = Me.Rw4.Value
    Drng.End(xlDown).Offset(0, 4).Value = Me.Rw5.Value
    Drng.End(xlDown).Offset(0, 5).Value = Me.Rw6.Value
    Drng.End(xlDown).Offset(0, 6).Value = Me.Rw7.Value
    Drng.End(xlDown).Offset(0, 7).Value = Me.Rw8.Value
    Drng.End(xlDown).Offset(0, 8).Value = Me.Rw9.Value
    Drng.End(xlDown).Offset(0, 9).Value = Me.Rw10.Value
    Drng.End(xlDown).Offset(0, 10).Value = Me.Rw11.Value
    Drng.End(xlDown).Offset(0, 11).Value = Me.Rw12.Value
End Sub

 

[steve the fish]

If you fill in a starting line manually it will all work. However if you don't want to do that how is the macro to know what the initial number is meant to be?

 

[steve the fish]

That line is now more complex than it needs to be. I cant see your sheet so I cant see the setup of it. It was a failsafe measure to make sure the last row found by xlup part was not before your first row.

 

[kelly mort]

If you fill in a starting line manually it will all work. However if you don't want to do that how is the macro to know what the initial number is meant to be?

It should take the initial number to be 0. If there is no data on the sheet. Because when i set the range to b7 instead of b6, it worked. The only isue here is that it left a blank row under the headers on the sheet then went to the next row before adding the data. So it we can get rid of that blank row. It will look just fine

 

[kelly mort]

That line is now more complex than it needs to be. I cant see your sheet so I cant see the setup of it. It was a failsafe measure to make sure the last row found by xlup part was not before your first row.

The data starts from B6 with headers.

 

[steve the fish]

Ok use this instead to assign lrrng.Value instead:

With Sheet1
    Set Drng = .Range("B6")
    Set lrRng = .Cells(.Rows.Count, Drng.Column).End(xlUp).Offset(1, 0)
    If Application.Count(Drng.EntireColumn) = 0 Then
        lrRng.Value = 0
    Else
        lrRng.Value = Application.Max(Drng.EntireColumn) + 1
    End If
    
    'insert rest here
End With

 

[kelly mort]

INDEX NAME DATE OF BIRTH A B C D E F G H I

This is how my sheet is set up
The macro you just gave me places a 0 under the first cell under the index. But that is where i want the 1 2 3 etc to show. I want that first row to take in data. Hope this helps
Kelly

 

[steve the fish]

That's exactly what its meant to do. Did you not just say 0 is the first number. 'It should take the initial number to be 0'. Read the macro and where it says 'insert rest here you need to insert the rest there.

 

[kelly mort]

Yes i said zero as an assumption. So now looking at the setup of my sheet, what solution do i get ? I think i am finding it hard to explain myself well. But the point is: how do i get rid of that empty row which is now taking the 0? While the sheet is having only the headers in, i want the macro put the number 1 under the INDEX. That is the headache i am having right now.
Thanks once again
Kelly

 

[steve the fish]

Like this?

With Sheet1
    Set Drng = .Range("B6")
    Set lrRng = .Cells(.Rows.Count, Drng.Column).End(xlUp).Offset(1, 0)
    lrRng.Value = Application.Max(Drng.EntireColumn) + 1
    'insert rest here
End With

 

[kelly mort]

Yes this placed the 1 at the right place just that the other data matched the 2 not 1. So the data took the index of 2. I want the 1 take the data row