使用worksheet.function将工作表公式更改为VBA公式 [英] Changing a Worksheet Formula to a VBA formula with worksheet.function

问题描述

目前，我有一个相对复杂的工作簿公式，我想将其转换为vba公式.

currently I have a relatively complex workbook formula, which I would like to translate to a vba formula.

当前，我编写了以下内容，但我希望它在代码执行期间基本评估函数并将该值插入所需的单元格中.

Currently, I wrote the following but I want it to basically evaluate the function during code execution and insert the value into the required cell.

我想这是worksheetfunction的某种组合，但是我不确定如何翻译

I imagine this is some combination of worksheetfunction but I am unsure of how to translate this

代码(无效)

MWS.Cells(LastMWSR + 1, ClastMWSC).Value = WorksheetFunction.SUMIFS(mws.range(N:N),mws.Range(B:B),worksheetfunction.INDEX(mws.range(B:B),worksheetfunction.MAX(worksheetfunction.INDEX((mws.range(C:C)=MWS.Cells(LastMWSR + 1, ClastMWSC - 1))*worksheetfunction.ROW(mws.range(C:C)),0))),mws.range(C:C),MWS.Cells(LastMWSR + 1, ClastMWSC - 1))

Excel公式

=SUMIFS($N:$N,$B:$B,INDEX($B:$B,MAX(INDEX(($C:$C=$AM41)*ROW($C:$C),0))),$C:$C,$AM41)

MWS是一个工作表
LastMWSR是工作表中的最后一行
CLASTMWSC是工作表中的最后一列

MWS is a worksheet
LastMWSR is the last row in the Worksheet
CLASTMWSC is the last column in the worksheet

欣赏您的想法

再次尝试公式

推荐答案

这里有三个问题

1. 无效的工作表引用mws.Range(...
2. 缺少引号Range(B:B)
3. 包围曝光错误

1. Invalid worksheet references mws.Range(...
2. missing quotes Range(B:B)
3. bad bracketing

重构，并添加一些简化派变量

Refactored, and adding some reductionist variables

Dim mws As Worksheet
Dim wf As WorksheetFunction
Dim mwsN As Range
Dim mwsC As Range
Dim mwsB As Range
Dim mwsLast As Range

Set wf = Application.WorksheetFunction
Set mws = Worksheets("mws")
Set mwsN = mws.Range("N:N")
Set mwsC = mws.Range("C:C")
Set mwsB = mws.Range("B:B")
Set mwsLast = mws.Cells(LastMWSR + 1, ClastMWSC - 1)

mws.Cells(LastMWSR + 1, ClastMWSC).Value = _
  wf.SumIfs(mwsN, mwsB, wf.Index(mwsB, wf.Max(wf.Index((mwsC = mwsLast) * wf.Row(mwsC), 0))), mwsC, mwsLast)

这篇关于使用worksheet.function将工作表公式更改为VBA公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！