Hi all - ok last question for today please...
I already have a userform but I would like to add a unique reference to each new record added and this to stay attached to that record (i.e. not reused if the data is resorted / row deleted etc..).
so far this is my code to enter textbox data to last row in datasheet:
I know it's not the prettiest but am (trying) to learn all this - very new to it..
I could use column A for the unique record. I thought of labelling what I have so far as RM1(drag down to last so shows RM2, RM3 etc...) then use =INDEX(A:A,MATCH(REPT("z",255),A:A)) to return the last RM number then tie VBA code to read this cell +1 to add to next cell but not sure if this is the best way (and have also got stuck on how I would adapt the above code to incorporate this?).
Any advice most appreciated! thank you all!
I already have a userform but I would like to add a unique reference to each new record added and this to stay attached to that record (i.e. not reused if the data is resorted / row deleted etc..).
so far this is my code to enter textbox data to last row in datasheet:
I know it's not the prettiest but am (trying) to learn all this - very new to it..
Code:
Private Sub cmdTransferVal_Click()
Dim lrVal As Long, lrList As Long, lrSales As Long, lrExc As Long
lrVal = Sheets("Valuations").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Valuations").Cells(lrVal + 1, "B").Value = cbxOfficeVal.Text
Sheets("Valuations").Cells(lrVal + 1, "C").Value = tbDateVal.Text
Sheets("Valuations").Cells(lrVal + 1, "D").Value = cbxValuer.Text
Sheets("Valuations").Cells(lrVal + 1, "E").Value = tbHouseVal.Text
Sheets("Valuations").Cells(lrVal + 1, "F").Value = tbStreetVal.Text
Sheets("Valuations").Cells(lrVal + 1, "G").Value = tbCityVal.Text
Sheets("Valuations").Cells(lrVal + 1, "H").Value = tbPostCodeVal.Text
Sheets("Valuations").Cells(lrVal + 1, "I").Value = tbVendorVal.Text
Sheets("Valuations").Cells(lrVal + 1, "J").Value = tbValueAmountVal.Text
If chbValLetter.Value = True Then
Sheets("Valuations").Cells(lrVal + 1, "K").Value = "Y"
Else
Sheets("Valuations").Cells(lrVal + 1, "K").Value = "N"
End If
Sheets("Valuations").Cells(lrVal + 1, "L").Value = cbxEnqSourceVal.Text
Sheets("Valuations").Cells(lrVal + 1, "M").Value = cbxDataSourceVal.Text
Sheets("Valuations").Cells(lrVal + 1, "N").Value = tbNotesVal.Text
End Sub
I could use column A for the unique record. I thought of labelling what I have so far as RM1(drag down to last so shows RM2, RM3 etc...) then use =INDEX(A:A,MATCH(REPT("z",255),A:A)) to return the last RM number then tie VBA code to read this cell +1 to add to next cell but not sure if this is the best way (and have also got stuck on how I would adapt the above code to incorporate this?).
Any advice most appreciated! thank you all!