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]

Something like this maybe:

Dim Drng As Range, lrRng As Range
With Sheet1
    Set Drng = .Range("B6")
    Set lrRng = .Cells(Application.Max(.Cells(.Rows.Count, Drng.Column).End(xlUp).Row, Drng.Row) + 1, Drng.Column)
    lrRng.Value = lrRng.Offset(-1, 0).Value + 1
    lrRng.Offset(0, 1).Value = Me.Rw2.Value
    lrRng.Offset(0, 2).Value = Me.Rw3.Value
    lrRng.Offset(0, 3).Value = Me.Rw4.Value
    lrRng.Offset(0, 4).Value = Me.Rw5.Value
    lrRng.Offset(0, 5).Value = Me.Rw6.Value
    lrRng.Offset(0, 6).Value = Me.Rw7.Value
    lrRng.Offset(0, 7).Value = Me.Rw8.Value
    lrRng.Offset(0, 8).Value = Me.Rw9.Value
    lrRng.Offset(0, 9).Value = Me.Rw10.Value
    lrRng.Offset(0, 10).Value = Me.Rw11.Value
    lrRng.Offset(0, 11).Value = Me.Rw12.Value
End With

 

[kelly mort]

When i run it i get type mismatch then it highlights this line:
lrRng.Value = lrRng.Offset(-1, 0).Value

This is when the sheet has only the headers. But when i add data to the first row after the headers, it works good. Can it be done so that that mismatch eeror is eliminated???
Regards
Kelly

 

[steve the fish]

I assume this:

lrRng.Offset(-1, 0).Value

isn't a number. So you cant add 1 to it.

 

[kelly mort]

I assume this:

lrRng.Offset(-1, 0).Value

isn't a number. So you cant add 1 to it.

Yes i noticed that. Any way around it?

 

[steve the fish]

Yeah. Put a number in B6 then run it.

 

[kelly mort]

B6 cotains the column header. Or i should start the range from b7 instead so that it points to the blank cell

 

[steve the fish]

Ok type a number in B7. That would work too.

 

[kelly mort]

When i set the range to b7 it worked when there is no data in sheet. Just that it left a blank row under the header. If that blank can be removed i think it will be great

 

[kelly mort]

Set lrRng = .Cells(Application.Max(.Cells(.Rows.Count, Drng.Column).End(xlUp).Row, Drng.Row) + 1, Drng.Column)

Can you explain what this line is doing? I cant get it clearly
Thanks