使用Excel VBA填充数据库，并按月划分付款 [英] Use Excel VBA to fill database with payments split in months

问题描述

我正在制作一个电子表格来控制自己的财务状况.我制作了一个VBA代码，在其中输入了4个不同的数据:i)客户名称；ii)合同总价值；iii)将支付合同的月数；iv)第一次付款的日期.

I'm making a spreadsheet to control my finances. I made a VBA code where I enter 4 different data: i) Client's name; ii) Total value of the contract; iii) Number of months that the contract will be paid; iv) Date of the first payment.

我已经能够完成并使用代码，该代码在4个不同的列中填充了这些信息.

I've been able to complete and use the code, that fills a database with these information in 4 different columns.

问题是，我希望它(当我单击插入"按钮时)自动拆分该值，并根据将要支付的月数(分期付款)填写日期.例如:

The thing is, I want it to (when I click the insert button) automatically split the value and fill the dates according to the number of months that it will be paid (payment in installments). For example:

USERFORM

1. 客户名称:John
2. 总价值:$ 1,000.00
3. 付款次数:4
4. 首次付款日期:2020年1月1日

也就是说，约翰将于2020年1月1日(1月)向我支付$ 250；2020年2月1日(2月)为250美元，依此类推...我希望它在数据库中显示如下内容:

That said, John will pay me $250 on 01/01/2020 (january); $250 on 02/01/2020 (february) and so on... and I want it to show on the data base something like this:

<身体>

客户端	值	日期-mm/dd/yyyy
克莱尔	2,000	12/05/2019(数据库中单笔付款的示例)
约翰	250	2020年1月1日
约翰	250	2020年2月1日
约翰	250	2020年3月1日
约翰	250	2020年4月1日
标记	500	2020年6月2日(示例)

我不知道该怎么做...有人可以帮助我吗?

And I don't have a clue how to do this... Can someone help me?

代码是:

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Projetos")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
'copy the data to the database
With ws
  .Cells(iRow, 2).Value = Me.boxCliente.Value
  .Cells(iRow, 3) = CCur(boxValor.Value)
  .Cells(iRow, 4).Value = Me.boxParcela.Value
  .Cells(iRow, 5) = CDate(boxData.Value)
End With

'clear the data
Me.boxCliente.Value = ""
Me.boxValor.Value = ""
Me.boxParcela.Value = ""
Me.boxData.Value = ""

End Sub

推荐答案

您可以尝试以下代码.

始终可以对此进行优化.也许有一些错误修正是可能的.但是，如果没有文本数据表，则不清楚.

Its always possible to optimize this. Maybe a little bugfix its possible. But without a text datasheet its not clear.

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Dim Name a string
Dim counter As Integer
Dim money As Double
Dim Date as Date
Dim i As Integer

Set ws = Worksheets("Projetos")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
'copy the data to the database
With ws
  .Cells(iRow, 2).Value = Me.boxCliente.Value 'Client
  .Cells(iRow, 3) = CCur(boxValor.Value) 'Money
  .Cells(iRow, 4).Value = Me.boxParcela.Value 'Number of Payments
  .Cells(iRow, 5) = CDate(boxData.Value) 'Date
  
  If .Cells(iRow, 4) <> 1 Then
    Name = .Cells(iRow, 2).Value
    counter = .Cells(iRow, 4).Value
    money = .Cells(iRow, 3).Value
    Date = .Cells(iRow, 5).Value
    
    For i = 0 To counter - 1
        .Cells(iRow + i, 2).Value = Name
        .Cells(iRow + i, 3).Value = money / counter
        .Cells(iRow + i, 5).Value = Format(DateAdd("m", i, Date), "mmddyyyy")
    Next i
        
End With

'clear the data
Me.boxCliente.Value = ""
Me.boxValor.Value = ""
Me.boxParcela.Value = ""
Me.boxData.Value = ""

End Sub

这篇关于使用Excel VBA填充数据库，并按月划分付款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！