Solved: Append function formula to range

[tlchan]

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]

[VBA]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
[/VBA]

[tlchan]

Thanks XLD

It works like a charm. Anyway why only R1C1 formula works and not using offset range ?

[Bob Phillips]

You could use Offset, but there is no need.