entry prepaid

[sna]

Hi Dear

I m now creating a template entry prepaid.it seems ok some part but i need to generate equal amount of prepaid.for example when click Prepaid button it will generate as in sheet prepaid.but I can think of allocation equal amount by month.

Note: the amount I type only not by code
Best Regards,

[snasui]

Please attach file with code and explain the result what you want.

[sna]

sorry for not having the attached file.
After inputing in the form on data,
I need to generate prepaid after click Prepaid button on userform.it will go to Prepaid with report as in attached.
Note I don't need formula since you used to give me .I need a vba to do that appears as value.

Best Regards

[snasui]

After inputing in the form on data

Please list all values that you fill in the form and show me the correct result after click prepaid button.

[sna]

The load form is used to transfer data from textbox to cells in Data Sheet by using Add,Search n Edit.the button Prepaid is used to transfer serial, Prepaid Account, Expense Account, Amount n description, Prepaid month from Data Sheet to Prepaid Sheet.in Prepaid Sheet from column G to I,
I need some code to generate amount of prepaid by prepaid (month) to column wise as in highlight.

Best Regards,

[sna]

My code not generate prepaid amortization from G4:R7 ....


Hope you may help


Thanks

[sna]

I can do with formula but how to write code to show the result like formula but only values


Thanks

[snasui]

The example code is below:

Code: Select all

'Other code
Dim r As Range, pm As Long
Dim am As Double, v As Double
j = 4
Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Prepaid")
ws2.Visible = True
lr = ws1.Range("A" & Rows.Count).End(xlUp).Row
For i = 6 To lr
    If ws1.Cells(i, 1).Value <> "" Then
        ws2.Cells(j, 1).Value = ws1.Cells(i, 1).Value
        ws2.Cells(j, 2).Value = ws1.Cells(i, 1).Offset(, 1).Value
        ws2.Cells(j, 3).Value = ws1.Cells(i, 1).Offset(, 2).Value
        ws2.Cells(j, 4).Value = ws1.Cells(i, 1).Offset(, 3).Value
        ws2.Cells(j, 5).Value = ws1.Cells(i, 1).Offset(, 4).Value
        ws2.Cells(j, 6).Value = ws1.Cells(i, 1).Offset(, 6).Value
        Set r = ws2.Cells(j, 7)
        pm = r.Offset(0, -1).Value
        am = r.Offset(0, -3).Value
        v = Round((1 / pm) * am, 2)
        Do While ws2.Cells(2, r.Column).Value <> ""
            If ws2.Cells(2, r.Column).Value > pm Then
                r.Value = ""
            ElseIf ws2.Cells(2, r.Column).Value < pm Then
                r.Value = v
            Else
                r.Value = am - (v * (pm - 1))
            End If
            Set r = r.Offset(0, 1)
        Loop
        j = j + 1
    End If
Next
'Other code...

[sna]

Thank you so much