CODEWITHSUNDEEP
vbaexcel
MGKP
MGKP

Reputation: 127

xlDown not working as intended

I've followed this tutorial

In order to handle button action. I want to write data to new row every time, on button click, but ths code either overwrites existing data or write data to a new rows (and again overwrites it).

code : 

Private Sub cmdUnesiUBazu_click()
     Sheet1.Activate

    Range("B2").End(xlDown).Offset(1, 0).Select ' want to start write from C2 cell
    ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1

    ActiveCell.Offset(0, 1).Value = txtSifraOsobe.Value
    ActiveCell.Offset(0, 2).Value = txtImeIPrezime.Value
    ActiveCell.Offset(0, 3).Value = txtAdresa.Value
    ActiveCell.Offset(0, 4).Value = cboGrad.Value
    ActiveCell.Offset(0, 5).Value = cboDrzava.Value
    ActiveCell.Offset(0, 7).Value = txtDatumRodjenja.Value

  End Sub

Upvotes: 3

Views: 5033

Answers (2)

brettdj
brettdj

Reputation: 55702

If would be much tidier to avoid the Activate and cell by cell Offsets, ie perhaps:

Dim ws As Worksheet
Dim rng1 As Range

Set ws = Sheets(1)
Set rng1 = ws.Cells(Rows.Count, "B").End(xlUp)
rng1.Offset(1, 1).Resize(1, 7) = Array(Me.txtSifraOsobe, Me.txtImeIPrezime, Me.txtAdresa, Me.cboGrad, Me.cboDrzava, , Me.txtDatumRodjenja)

Upvotes: 1

user6432984
user6432984

Reputation:

It is better to refer to cells directly, instead of selecting them and referring to the ActiveCell. Watch Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)

It looks like you are trying to make a unique identifier (ID) to refer to your records. But you are adding 1 to the empty cell below: ActiveCell.Value = ActiveCell.Offset(1, 0).Value + 1 instead you should be adding 1 to the value of the cell above ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1

Sheet1.Activate

With Range("B2").End(xlDown).Offset(1, 0)
    .Value = .Offset(-1, 0).Value + 1
    .Offset(0, 1).Value = txtSifraOsobe.Value
    .Offset(0, 2).Value = txtImeIPrezime.Value
    .Offset(0, 3).Value = txtAdresa.Value
    .Offset(0, 4).Value = cboGrad.Value
    .Offset(0, 5).Value = cboDrzava.Value
    .Offset(0, 7).Value = txtDatumRodjenja.Value
End With

Upvotes: 2

Related Questions