Page 1 of 1
entry prepaid
Posted: Fri Dec 25, 2020 1:23 pm
by 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,
Re: entry prepaid
Posted: Fri Dec 25, 2020 4:25 pm
by snasui
Please attach file with code and explain the result what you want.
Re: entry prepaid
Posted: Fri Dec 25, 2020 5:15 pm
by 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
Re: entry prepaid
Posted: Fri Dec 25, 2020 5:24 pm
by snasui
sna wrote: Fri Dec 25, 2020 5:15 pm
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.
Re: entry prepaid
Posted: Fri Dec 25, 2020 8:18 pm
by 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,
Re: entry prepaid
Posted: Sat Dec 26, 2020 8:57 pm
by sna
My code not generate prepaid amortization from G4:R7 ....
Hope you may help
Thanks
Re: entry prepaid
Posted: Sat Dec 26, 2020 9:19 pm
by sna
I can do with formula but how to write code to show the result like formula but only values
Thanks
Re: entry prepaid
Posted: Sun Dec 27, 2020 7:12 am
by 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...
Re: entry prepaid
Posted: Sun Dec 27, 2020 8:18 am
by sna
Thank you so much
