VBA voor regel terug zetten

Edria

Your code writes a booking row from sheet "Data" to sheet "Booking" but from Q45 down. My idea is to use cell Q43 (now "Row") and cell Q44 to capture the row number of the record to be updated. 

In the attached revised file, the macro behind the "Update booking" on Data is modified as in bold below:  

Sub Reset_Line()

    ' check a non-blank cell was chosen
    If Selection.Rows.Count > 1 Or Range("Data!A2") = "" Then
            MsgBox "Please click a single reference in column A"
           Exit Sub
    End If

    '-- Search
    Dim f As Range
    Set f = Range("Data!A:A").Find(Range("Data!A2"))

    If f Is Nothing Then Exit Sub

    ' record row in cell Q44
    Range("Booking!Q44").Value = f.Row
    '-- Vertical
    Dim t As Range
    Set t = Range("Booking!Q45")


    t.Resize(35) = WorksheetFunction.Transpose(f.Resize(, 35))

    ' recommend NOT clearing row- let them be overwriten if needed.
'    f.Resize(, 35).ClearContents
'    f.Offset(, 1) = "Update"
<< existing code>>
     ' move to edit
     Worksheets("Booking").Activate

End Sub

So if A8 is picked on Data, Q44 becomes 8. If more than one row is picked, an error message is given.

Now the row isn't cleared but the record can be editted on Booking. When the button "Save/Update" is clicked, the record on Data is overwritten by this revised macro:

Sub Booking_Update()

    'if updating a booking, write back to row
    With Sheets("Data").Cells(Range("Q44").Value, 1)
        .Resize(, 35) = Array([Q9].Value, [Q11].Value, [Q13].Value, [Q15].Value, [Q17].Value, [Q21].Value, [Q19].Value, [Q23].Value, [D9].Value, [D11].Value, [D13].Value, [D15].Value, [D17].Value, [D19].Value, [D21].Value, [D23].Value, [D25].Value, [D27].Value, [D29].Value, [D31].Value, [k9].Value, [K11].Value, [K13].Value, [K15].Value, [K17].Value, [K19].Value, [K21].Value, [K23].Value, [K25].Value, [K27].Value, [K29].Value, [K31].Value, [Q25].Value, [Q29].Value, [Q27].Value)
    End With

    Range("Q9,Q11,Q13,Q15,Q19,Q17,Q21,D9,D11,D13,D15,D17,D19,D21,D23,D25,D27,D29,D31,k9,K11,K13,K15,K17,K19,K21,K21,K23,K25,K27,K29,K31,Q23,Q27,Q25,Q29").ClearContents

    ' reset row cell
    Range("Q44").Value = ""
    ' go back
    Worksheets("Data").Activate
     ' tell user
    MsgBox "Updated record"
End Sub

where the part Cells(Range("Q44").Value, 1) means Cells(8, 1) or A8. That is resized and overwritten. If you press the button (or Delete), the original record remains.

You can try this with the record you provided in Booking (and I wrote to the Data sheet, row 8).

Note that the part:

    ' reset row cell
    Range("Q44").Value = ""

is added to other modules too.

Hope this is what you want. If so, please be sure to mark this Answer as "Selected". After, you can also add comments if you like.

Hello!

I hope you can help me, i appreciate alot.

I have a text description, that i have to write it with html tags.

Is there a formula in excel to change all the upper letters from this text like: SET, SIDED, DOUBLE, SPECIFICATION, etc"  With this html tag before and after "<strong> </strong>" ?

Thanks for help!

<p>SET - colored markers make it easy to mark important information, but usually the available colors run out quickly. This set includes as many as 40 pieces of highlighters, which will surely meet the expectations of the most demanding people.<br />DOUBLE-SIDED - they are very practical two-sided highlighters. WITH on one side they have a round head, which will be perfect for writing or drawing, on the other side they have an oblique head, which is perfect for highlighting important information.<br />VERY EFFICIENT CARTRIDGE - these high-performance pens have a special refill that it is enough even for 300 m , so they will surely serve you for a long time in perfect condition.<br />CASE INCLUDED - colorful markers are packed in a very practical case secured with a lock. It makes it easier to keep your highlighters tidy and you can keep them close at hand without the risk of losing half of them .<br />SPECIFICATION - number of pieces: 40 pcs; two-sided: yes; case included: yes; slanted head: yes; round head: yes; cartridge efficient for 300 m: yes; dimensions: 16/16 / 9.5cm; weight of the set: 0.895 kg<br

 />SPECIFICATION:</p>

<p>number of pieces: 40 pcs<br />two-sided: yes<br />case included: yes<br />slanted head: yes<br />round head: yes<br />efficient cartridge for 300 m: yes<br />dimensions: 16/16 / 9.5cm<br />weight of the set: 0.8950 kg</p>