tlchan
09-16-2012, 08:49 PM
Hi there,
I'm try enter data through userform where the data will append to worksheet as database. In column G,J L I need to insert function formula MROUND to complete the data sheet.
How should accomplish this task?
Bob Phillips
09-17-2012, 12:53 AM
Private Sub Cmd_Addnew_Click()
Dim strLastRow As Long
Application.ScreenUpdating = False
strLastRow = Worksheets("asbcust").Range("A65536").End(xlUp).Row + 1
With Worksheets("asbcust")
.Cells(strLastRow, 1).Value = Me.TB_AANo.Value
.Cells(strLastRow, 2).Value = Me.Tb_Name.Value
.Cells(strLastRow, 3).Value = Me.Tb_ICNo.Value
.Cells(strLastRow, 4).Value = Me.Tb_Addr.Value
.Cells(strLastRow, 5).Value = Me.Tb_LODate.Value
.Cells(strLastRow, 6).Value = Me.Tb_AAloan.Value
.Cells(strLastRow, 8).Value = Me.Tb_MRTA.Value
.Cells(strLastRow, 9).Value = Me.Tb_totloan.Value
.Cells(strLastRow, 13).Value = Me.Tb_LOacceptdate.Value
.Cells(strLastRow, 11).Value = Me.Tb_Sales.Value
.Cells(strLastRow, 27).Value = Me.Tb_Mthinstl.Value
.Cells(strLastRow, 28).Value = Me.Tb_tenure.Value
.Cells(strLastRow, "G").FormulaR1C1 = "=Mround(RC[-1],100000)"
.Cells(strLastRow, "J").FormulaR1C1 = "=Mround(RC[-1],100000)"
.Cells(strLastRow, "L").Formular1r1 = "=Mround(RC[-1],100000)"
End With
'Empty textboxes
With Me
.Tb_Name.Value = vbNullString
.Tb_ICNo.Value = vbNullString
.Tb_Addr.Value = vbNullString
.Tb_LODate.Value = vbNullString
.Tb_AAloan.Value = vbNullString
.Tb_Sales.Value = vbNullString
.TB_AANo.Value = vbNullString
.Tb_MRTA.Value = vbNullString
.Tb_totloan.Value = vbNullString
.Tb_LOacceptdate.Value = vbNullString
.Tb_Mthinstl.Value = vbNullString
.Tb_tenure.Value = vbNullString
End With
MsgBox "Record added to database", vbInformation, "Data Added"
Application.ScreenUpdating = True
End Sub
tlchan
09-17-2012, 08:33 AM
Thanks XLD
It works like a charm. Anyway why only R1C1 formula works and not using offset range ?
Bob Phillips
09-17-2012, 02:45 PM
You could use Offset, but there is no need.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.