vb help

Please can you help me change the code on my worksheet?

I need to change the code that is linked to the userform:-

next_empty.Offset(, 14) = txt1
next_empty.Offset(, 15) = txt2

At the moment textbox txt1 & txt2 input there values into "Data" columns 14 & 15

I want the dates in txt1 & txt2 to go to "Dates" (Sheet3) Columns B & C

The trouble is that the first occurrence on Data sheet occurs at line 14, I then need txt1 & txt2 to input -12 in order for them to appear on line 2

The current line (21) would hopefully into txt1 & txt2 to lines 9 "Dates"

Many thanks for your help

Private Sub cmdPopulateRow_Click()
    Sheets("Data").Unprotect Sheets("DataSheet").Range("C27").Value
    
    Dim next_empty As Range
    
    '~~> Get next empty row
    If Sheets("Data").Range("B14") <> "" Then
        Set next_empty = Sheets("Data").Range("B13").End(xlDown).Offset(1)
    Else
        Set next_empty = Sheets("Data").Range("B14")
    End If
    
    'fill in details
    next_empty = txtrecieveddate
    next_empty.Offset(, 2) = TextBox3.Value
    next_empty.Offset(, 3) = ComboBox1
    next_empty.Offset(, 4) = txtCustomerusername
    next_empty.Offset(, 5) = txtcustomersname
    next_empty.Offset(, 6) = TextBox5
    next_empty.Offset(, 7) = ComboBox2
    next_empty.Offset(, 8) = ComboBox3
    next_empty.Offset(, 9) = txtstarteddate
    next_empty.Offset(, 10) = txtcompleteddate
    next_empty.Offset(, 11) = "Yes"
    
    next_empty.Offset(, 14) = txt1
    next_empty.Offset(, 15) = txt2
    
    Sheets("Data").Protect Sheets("DataSheet").Range("C27").Value
    Unload Me
End Sub

Open in new window

Gazza83Asked:
Who is Participating?
 
DaveConnect With a Mentor Commented:
pls change

next_empty.Offset(, 14) = txt1
 next_empty.Offset(, 15) = txt2


to this

hth

Dave

Sheets("Dates").Cells(next_empty.Row - 12, "B") = txt1
    Sheets("Dates").Cells(next_empty.Row - 12, "C") = txt2

Open in new window

0
 
DaveCommented:
Gary,

Can you pls post your file

For example I can't see how
txtrecieveddate
works

Cheers

Dave
0
 
Gazza83Author Commented:
Sorry Dave, my bad...forgot to upload the file.

Thanks,

Gaz
thursday.xls
0
 
Gazza83Author Commented:
Thanks Dave, your code works perfectly as always.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.