- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我是一名会计师,我需要每天将每个客户的付款与未结发票进行匹配,我在这个网站上找到了 Michael Schwimmer 发布的一个非常漂亮和优雅的 VBA 代码。 https://berndplumhoff.gitbook.io/sulprobil/excel/excel-vba-solutions/accounts-receivable-problem
代码运行完美,它可以自动计算并列出相加到特定总和的结果。但是,我希望 VBA 代码也返回发票号。代码将值数组传递给函数进行计算,然后将可能的解决方案返回到列 E,我不了解数组,所以不知道如何将发票编号数组传递给函数并返回结果。有人能帮忙吗?代码如下,也可以到link下载excel工作簿我提供了。提前致谢!
Private Sub cmbCalculate_Click()
Dim dGoal As Double
Dim dTolerance As Double
Dim dAmounts() As Double
Dim vResult As Variant
Dim m As Long
Dim n As Long
With Me
dGoal = .Range("B2")
dTolerance = .Range("C2")
ReDim dAmounts(1 To 100)
For m = 2 To 101
If (.Cells(m, 1) <> "") And (IsNumeric(.Cells(m, 1))) Then
dAmounts(m - 1) = .Cells(m, 1)
Else
ReDim Preserve dAmounts(1 To m - 1)
Exit For
End If
Next
ReDim Preserve dAmounts(1 To UBound(dAmounts) - 1)
vResult = Combinations(dAmounts, dGoal, dTolerance)
Application.ScreenUpdating = False
.Range("D3:D65536").ClearContents
.Range(.Cells(3, 4), .Cells(UBound(vResult) + 3, 4)) = vResult
Application.ScreenUpdating = True
End With
End Sub
Function Combinations( _
Elements As Variant, _
Goal As Double, _
Optional Tolerance As Double, _
Optional SoFar As Variant, _
Optional Position As Long) As Variant
Dim i As Long
Dim k As Long
Dim dCompare As Double
Dim dDummy As Double
Dim vDummy As Variant
Dim vResult As Variant
If Not IsMissing(SoFar) Then
'Sum of elements so far
For Each vDummy In SoFar
dCompare = dCompare + vDummy
Next
Else
'Start elements sorted by amount
For i = 1 To UBound(Elements)
For k = i + 1 To UBound(Elements)
If Elements(k) < Elements(i) Then
dDummy = Elements(i)
Elements(i) = Elements(k)
Elements(k) = dDummy
End If
Next
Next
Set SoFar = New Collection
End If
If Position = 0 Then Position = LBound(Elements)
For i = Position To UBound(Elements)
'Add current element
SoFar.Add Elements(i)
dCompare = dCompare + Elements(i)
If Abs(Goal - dCompare) < (0.001 + Tolerance) Then
'Goal achieved
k = 0
ReDim vResult(0 To SoFar.Count - 1, 0)
For Each vDummy In SoFar
vResult(k, 0) = vDummy
k = k + 1
Next
Combinations = vResult
Exit For
ElseIf dCompare < (Goal + 0.001 + Tolerance) Then
'Enough room for another element
'Call recursively starting with next higher amount
vResult = Combinations(Elements, Goal, Tolerance, SoFar, i + 1)
If IsArray(vResult) Then
Combinations = vResult
Exit For
Else
SoFar.Remove SoFar.Count
dCompare = dCompare - Elements(i)
End If
Else
'Amount too high
SoFar.Remove SoFar.Count
Exit For
End If
Next 'Try next higher amount
End Function
最佳答案
您可能只需使用 VLOOKUP 即可获得发票编号,但这里是一个 VBA 解决方案。我已将 Sofar
集合中的值从发票金额更改为该金额的索引号。该索引号然后从新数组 InvNo
中给出相应的发票号。
更新 - 按截止日期排序
Sub cmbCalculate_Click()
Dim ws As Worksheet, dAmounts() As Double, sInvno() As String
Dim i As Long, dSum As Double
Dim dtDue() As Date
Set ws = Me
i = ws.Cells(Rows.Count, "A").End(xlUp).Row
ReDim dAmounts(1 To i - 1)
ReDim sInvno(1 To i - 1)
ReDim dtDue(1 To i - 1)
' fill array
For i = 1 To UBound(dAmounts)
dAmounts(i) = ws.Cells(i + 1, "A")
sInvno(i) = ws.Cells(i + 1, "B")
dtDue(i) = ws.Cells(i + 1, "C")
dSum = dSum + dAmounts(i)
Next
' sort array
Call BubbleSort(dAmounts, sInvno, dtDue)
Dim n: For n = LBound(dAmounts) To UBound(dAmounts): Debug.Print n, dAmounts(n), sInvno(n), dtDue(n): Next
Dim dGoal As Double, dTolerance As Double, vResult As Variant
dGoal = ws.Range("D2")
dTolerance = ws.Range("E2")
' check possible
If dGoal > dSum Then
MsgBox "Error : Total for Invoices " & Format(dSum, "#,##0.00") & _
" is less than Goal " & Format(dGoal, "#,##0.00")
Else
' solve and write to sheet
vResult = Combinations2(dAmounts, sInvno, dtDue, dGoal, dTolerance)
If IsArray(vResult) Then
With ws
.Range("F3:H" & Rows.Count).ClearContents
.Range("F3").Resize(UBound(vResult), 3) = vResult
End With
MsgBox "Done"
Else
MsgBox "Cannot find suitable combination", vbCritical
End If
End If
End Sub
Function Combinations2( _
Elements As Variant, _
Invno As Variant, _
Due As Variant, _
Goal As Double, _
Optional Tolerance As Double, _
Optional SoFar As Variant, _
Optional Position As Long) As Variant
Dim i As Long, n As Long, dCompare As Double
' summate so far
If IsMissing(SoFar) Then
Set SoFar = New Collection
Else
For i = 1 To SoFar.Count
dCompare = dCompare + Elements(SoFar(i))
Next
End If
If Position = 0 Then Position = LBound(Elements)
For i = Position To UBound(Elements)
SoFar.Add CStr(i)
dCompare = dCompare + Elements(i)
' check if target achieved
If Abs(Goal - dCompare) < (0.001 + Tolerance) Then
'Goal achieved
Dim vResult As Variant
ReDim vResult(1 To SoFar.Count, 1 To 3)
For n = 1 To SoFar.Count
vResult(n, 1) = Elements(SoFar(n))
vResult(n, 2) = Invno(SoFar(n))
vResult(n, 3) = Due(SoFar(n))
Next
Combinations2 = vResult
ElseIf dCompare < (Goal + 0.001 + Tolerance) Then
'Enough room for another element
'Call recursively starting with next higher amount
vResult = Combinations2(Elements, Invno, Due, Goal, Tolerance, SoFar, i + 1)
If IsArray(vResult) Then
Combinations2 = vResult
Exit For
Else
SoFar.Remove SoFar.Count
dCompare = dCompare - Elements(i)
End If
Else
'Amount too high
SoFar.Remove SoFar.Count
Exit For
End If
Next
End Function
Sub BubbleSort(ByRef ar1 As Variant, ByRef ar2 As Variant, ByRef ar3 As Variant)
' sort both arrays
Dim d, s, i As Long, k As Long, dt As Date
For i = 1 To UBound(ar1)
For k = i + 1 To UBound(ar1)
If (ar1(k) < ar1(i)) Or _
(ar1(k) = ar1(i) _
And ar3(k) < ar3(i)) Then
d = ar1(i)
ar1(i) = ar1(k)
ar1(k) = d
s = ar2(i)
ar2(i) = ar2(k)
ar2(k) = s
dt = ar3(i)
ar3(i) = ar3(k)
ar3(k) = dt
End If
Next
Next
End Sub
关于arrays - 在VBA中返回数组的函数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/68364052/
在 C 中: int a[10]; printf("%p\n", a); printf("%p\n", &a[0]); 产量: 0x7fff5606c600 0x7fff5606c600 这是我所期望
我一直在尝试运行此循环来更改基于数组的元素的位置,但出现以下错误。不太确定哪里出了问题。任何想法或想法!谢谢。 var population = [[98, 8, 45, 34, 56], [9, 1
我正在尝试获取一个 Ruby 数组数组并将其分组以计算其值。 数组有一个月份和一个 bool 值: array = [["June", false], ["June", false], ["June"
所以我们的目标是在遇到某个元素时将数组分割成子数组下面的示例 array.split("stop here") ["haii", "keep", "these in the same array bu
在this问题已经回答了两个表达式是相等的,但在这种情况下它们会产生不同的结果。对于给定的 int[] 分数,为什么会这样: Arrays.stream(scores) .forEac
我认为我需要的是哈希数组的数组,但我不知道如何制作它。 Perl 能做到吗? 如果是这样,代码会是什么样子? 最佳答案 perldoc perldsc是了解 Perl 数据结构的好文档。 关于arra
我遇到了这个问题,从 API 中我得到一个扩展 JSON,其中包含一个名为坐标的对象,该对象是一个包含数组 o 数组的数组。 为了更清楚地看这个例子: "coordinates": [
postgres 中有(v 9.5,如果重要的话): create table json_test( id varchar NOT NULL, data jsonb NOT NULL, PRIM
我用 echo "${array[@]}" 和 echo "${array[*]}" 得到了相同的结果。 如果我这样做: mkdir 假音乐; touch fakemusic/{Beatles,Sto
我正在尝试创建 typealias 对象的数组数组 - 但我收到“表达式类型不明确,没有更多上下文”编译错误。这是我的代码: typealias TestClosure = ((message: St
如果您在 Python 中创建一维数组,使用 NumPy 包有什么好处吗? 最佳答案 这完全取决于您打算如何处理数组。如果您所做的只是创建简单数据类型的数组并进行 I/O,array模块就可以了。 另
当我将数组推送到只有一个数组作为其唯一元素的数组数组时,为什么会得到这种数据结构? use v6; my @d = ( [ 1 .. 3 ] ); @d.push( [ 4 .. 6 ] ); @d.
在 Julia 中,我想将定义为二维数组向量的数据转换为二维矩阵数组。 如下例所述,我想把数据s转换成数据t,但是至今没有成功。 我该如何处理这个案子? julia> s = [[1 2 3], [4
C 没有elementsof 关键字来获取数组的元素数。所以这通常由计算 sizeof(Array)/sizeof(Array[0]) 代替但这需要重复数组变量名。1[&Array] 是指向数组后第一
所以,假设我有一个像这样的(愚蠢的)函数: function doSomething(input: number|string): boolean { if (input === 42 || in
我有以下数组: a = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16] 我将它用于一些像这样的视觉内容: 1 2 3 4 5 6 7 8 9 10
我想知道数组中的 .toList 与 .to[List] 之间有什么区别。我在spark-shell中做了这个测试,结果没有区别,但我不知道用什么更好。任何意见? scala> val l = Arr
我很难获得完全相同对象的多个元素的当前元素索引: $b = "A","D","B","D","C","E","D","F" $b | ? { $_ -contains "D" } 替代版本: $b =
我正在尝试使用来自我的 API 的 v-select 执行 options,我将数据放在数组数组中。 Array which I got from API 它应该是一个带有搜索的 select,因为它
这个问题在这里已经有了答案: String literals: pointer vs. char array (1 个回答) 4 个月前关闭。 当我执行下一个代码时 int main() {
我是一名优秀的程序员,十分优秀!