next open row

[joacro]

Hi there,

Someone assist me in this code, where the entries of txtboxes go into the next availible open row:

Private Sub CmdAdd_Click()

LR = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & LR) = txtPrin
Range("A" & LR + 1) = txtSpouse
Range("A" & LR + 2) = txtCh1
Range("A" & LR + 3) = txtCh2
Range("A" & LR + 4) = txtCh3
Range("A" & LR + 5) = txtCh4
Range("A" & LR + 6) = txtCh5
Range("A" & LR + 7) = txtCh6
End Sub

It is working perfectly, however I need txtTotal to go into the same Row as txtPrin but in Colum "I"

Hope someone can assist me in this

Kind regards

Joacro

 

[VoG]

Try

Range("I" & LR) = txtTotal

 

[joacro]

Hi Vog,

Thank you, the code put the value into I,

However, If I delete the values on the sheet it self, the above code just continue to insert the code where the last one was pasted.

Any solution to this

 

[VoG]

I need txtTotal to go into the same Row as txtPrin but in Colum "I"

That is exactly what the code that I posted does. Are you saying that you want something different? If so what exactly?

 

[joacro]

Hi Peter,

Your code is perfect,

If I delete the value on the spreadsheet itself and add new values as per userform, the code continue to past the values in the last row where it pasted the previous values.

I need the code to paste in the first empty row.

Does it make any sense??

 

[VoG]

Try

Range("I" & Rows.Count).End(xlUp).Offset(1).Value = txtTotal

 

[joacro]

Hi Peter,

I think you understand me wrong.

your first code is just perfect.

I have tested the code a few time. Now I have clear all the values on the sheet to start again from say A1, but it keeps on pasting the new values on say A44 where the last value was pasted.

Basically I need to reset the sheet

 

[VoG]

How are you clearing the values? You need to select them then press Delete

 

[joacro]

Peter,

I need to convert txtTotal's format to Number

Any idee as to how to do that?

 

[joacro]

How are you clearing the values? You need to select them then press Delete

Yes I am selecting the value and then pressing delete