Textbox macro to update data in specific range

[Bimmy]

Hi,

I have a userform with 11 textboxes and 1 command button,

Data entered in the first 10 textboxes are updated in specific columns on sheet1 from row 2 onwards.

Below is the code

Const F = "dd-mm-yy hh:mm"


Private Sub CommandButton1_Click()
    N% = Val(TextBox11.Text)
    If N < 1 Then TextBox10.SetFocus: Beep: Exit Sub
     V = [{8,11,12,13,16,19,20,21,22,23}]
    r& = Sheet2.Cells(Sheet2.Rows.Count, V(1)).End(xlUp)(2).Row
For c% = 1 To UBound(V)
    Sheet2.Cells(r, V(c)).Resize(N).Value = Me.Controls("TextBox" & c).Text
    Me.Controls("TextBox" & c).Text = ""
Next
    UserForm_Initialize
End Sub


Private Sub TextBox2_Enter()
    TextBox2.Text = Format$(Now, F)
End Sub


Private Sub UserForm_Initialize()
     TextBox3.Text = Format$(Now, F)
    TextBox11.Text = "1"
End Sub
Private Sub TextBox4_Enter()
    TextBox4.Text = Format$(Now, F)
End Sub

Since range D2:M19 will be used for manual updation of data, I would like the macro to update the data in range D20:M30.

Kindly assist.

Have attached sample sheet.

 

[Nebu]

Hi:
".End(xlUp)(2)" this part of your code is basically for offsetting the rows, if you want to get the data from 20th row drag you headings down to the 19th row.

Thanks

 

[Bimmy]

Hi Nebu,

Thanks for responding.

The heading will be on row 1 and will not change.

There are few comments and data's that needs to be manually updated in range D2:M19. That's why I want the macro to be update data inserted in userform from range D20:M30.

 

[Nebu]

Hi:

Here is a cheeky way of doing this. Copy the heading row to row 19 color the font as white. the macro will paste the data from row 20.

Thanks

 

[Bimmy]

Thanks...

 

[Marc L]

Hi !

Other way is to combine End property with MIN worksheet function …
​