关于用VBA替代数组公式的问题

小白兔3399

大家好：

我想把数组公式写进VBA,  发现运行以后单元格的引用位置全错了，不明白什么原因。各位大神还没有更好的代码可以实现这个计算呀。感谢 物料需求表.zip (32.13 KB, 下载次数: 12)

2023-4-24 21:09 上传

点击文件名下载附件

Private Sub CommandButton1_Click()
Dim r As Long
Application.ScreenUpdating = False
AutoFilterMode = False '.取消筛选状态
r = Range("C1048576").End(xlUp).Row

Range("H4:H" & r).FormulaArray = "=SUM((标准用量!$B$3:$B$6258=$B4)*(标准用量!$C$3:$C$6258=$C4)*IF(标准用量!$H$2:$CT$2=H$3,标准用量!$H$3:$CT$6258))"
Range("I4:I" & r).FormulaArray = "=SUM((标准用量!$B$3:$B$6258=$B4)*(标准用量!$C$3:$C$6258=$C4)*IF(MONTH(标准用量!$H$2:$CT$2)=I$3,标准用量!$H$3:$CT$6258))"
Range("J4:J" & r).FormulaArray = "=SUM((标准用量!$B$3:$B$6258=$B4)*(标准用量!$C$3:$C$6258=$C4)*IF(MONTH(标准用量!$H$2:$CT$2)=J$3,标准用量!$H$3:$CT$6258))"
Range("K4:K" & r).FormulaArray = "=SUM((标准用量!$B$3:$B$6258=$B4)*(标准用量!$C$3:$C$6258=$C4)*IF(MONTH(标准用量!$H$2:$CT$2)=K$3,标准用量!$H$3:$CT$6258))"

Application.ScreenUpdating = True
End Sub

你地铁图

vba计算完之后填结果不就行了

小白兔3399

你地铁图 发表于 2023-4-24 22:34
vba计算完之后填结果不就行了

不大明白，可否详解，感谢

于箱长

你这个是多条件求和？公式写得好棒

于箱长

在别的网站搜的四种方式，仅供选择，还有其他的方法，比如不写公式直接字典+数组循环多条件求和等等，这种写入公式的方式根本上解决不了速度慢的问题

tanglf188

建议用数组或字典，如果用公式的话可以在代码开始插入如下代码，关闭自动计算：
    Application.Calculation = xlManual
    然后，要将它们切换回自动，请确保将这行代码放在宏的底部:
    Application.Calculation = xlAutomatic

3190496160

既然用代码了，就没有必要再写入大量的数组公式，这样会导致文件很大，反应很慢，代码运行速度更慢，
具体描述你的需求，会有人帮你写代码的，只要不是工作量很大

于箱长

字典+数组给你码一个，仅供参考

小白兔3399

于箱长 发表于 2023-4-27 10:59
字典+数组给你码一个，仅供参考

太厉害了，非常感谢。

可否请老师再帮忙优化一下，就是我测试中发现，如果是统计表中的部门，在标准用量表中不存在时，单元格显示的计算结果是空的，我想让计算结果直接显示为0.  

于箱长

小白兔3399 发表于 2023-4-27 22:40
太厉害了，非常感谢。

可否请老师再帮忙优化一下，就是我测试中发现，如果是统计表中的部门，在标准用 ...