excel - How fill in the next row of a list in a spreadsheet using a macro? -

i need write spreadsheet when press button adds row of data , asks parameters needed calculations, cant seem fill in data in next row down? complete begginner macros in excel , have done basic programming matlab on uni course. script far follows:

sub addpostol()  ' addpostol macro   dim rngseek range  set rngseek = range("b1")  while rngseek <> ""     'if cell isn't empty, drop down 1 row:     rngseek = rngseek.offset(1, 0) end  rngseek.offset(0, 1)    .font        .name = "solid edge ansi1 symbols"        .size = 11        .value = 1    end                                            <--added  end  'don't need call range() around rngseek - range type  rngseek.offset(0, 3) = "=rc[-1]" rngseek.offset(0, 4) = "0"  rngseek.offset(1, 1)     .font.bold = true                <--don't need if 1 statement end  'can use statement here if want:  rngseek      .offset(1, 1) = "x value"     .offset(2, 1) = "y value"      .offset(0, 4) = "=2*sqrt((r[1]c[-3]-r[1]c)^2+(r[2]c[-3]-r[2]c)^2)"     .offset(0, 5) = "=2*sqrt((r4c3-r[1]c)^2+(r5c3-r[2]c)^2)"     .offset(0, 6) = "=2*sqrt((r[1]c[-3]-r[1]c)^2+(r[2]c[-3]-r[2]c)^2)"     .offset(0, 7) = "=2*sqrt((r[1]c[-3]-r[1]c)^2+(r[2]c[-3]-r[2]c)^2)"     .offset(0, 8) = "=2*sqrt((r4c3-r[1]c)^2+(r5c3-r[2]c)^2)"      .offset(0, 2) = (inputbox("insert positional tolerance diametre"))     .offset(1, 2) = (inputbox("insert x value on drawing"))     .offset(2, 2) = (inputbox("insert y value on drawing"))  end  end sub 

ok, i'm going refactor code bit make more readable, , try , answer question too.

firstly, believe lastrow = worksheets("sheet1").rows.count return number of rows in worksheet, not number of rows have filled in. when ran line got 1048576! find first empty row, need find column have value filled in each row; then, cycle down column looking empty cell: first empty row:

dim rngseek range  set rngseek = range("a1") <--your starting cell  while rngseek <> ""     'if cell isn't empty, drop down 1 row:     rngseek = rngseek.offset(1, 0) wend  'rngseek sitting @ first row has blank in column 

so technique find first row looking for. then, populate row data. can leverage other elements code snippet above make things easier when updating: specifically, .offset method:

your code:

with rngseek.offset(0, 2)     .font         .name = "solid edge ansi1 symbols"         .size = 11     end      .value = 1 end  'repeat cells: no need select them first, use .offset 

fyi .offset goes so:

range("cell reference").offset(rows, columns) 

where 'cell reference' can cell, 'a1' or 'd24', rows number of rows offset to right (use negative number offset left), , columns number of columns offset down (use negative offset up). value of .offset(0, 0) offsets nowhere.

edit: may not use column seek first blank cell; if so, adapt offset values reflect cell want change value of. value of 1 offset 1 column right or 1 row down, , -1 offset 1 column left or 1 row up.

addition: better use range("a1").end(xldown) instead of loop find first blank cell. set range variable equal this, , replace loop with:

set rngseek = range("a1").end(xldown).offset(1, 0) 

you can use rngseek way described above. (you can rename rngseek variable name also).