excel - VB.Net Excel公式范围动态？

Question

您好我想使用 Vb.net 在 Excel SUMIFS 公式中创建一个动态范围。

这是我的代码

Imports Excel = Microsoft.Office.Interop.Excel
Dim wsCopyHere as Worksheet 'All Worksheet and Workbooks are correct
Dim dueValue, lRow, bidRow as long
lRow = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row()
bidRow = lRow + 18
dueValue = bidRow + 1
With wsCopyHere.Range("J" & bidRow + 1)
    .Formula = "= SUMIFS(J20: J ""dueValue"", I19: I35, I39, H19: H35, K39)"
    .Font.Bold = True
    .Font.Italic = True
End With

这是我使用的另一种方法，它什么都不返回

Imports Excel = Microsoft.Office.Interop.Excel
Dim xlApp As New Excel.Application
Dim wsCopyHere as Worksheet 'All Worksheet and Workbooks are correct
Dim dueValue, lRow, bidRow as long    
lRow = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row()
bidRow = lRow + 18
dueValue = bidRow + 1
With wsCopyHere.Range("J" & bidRow + 1)
    .Value = xlApp.WorksheetFunction.SumIfs(wsCopyHere.Range("J20:J" & dueValue), wsCopyHere.Range("I20:I" & dueValue), wsCopyHere.Range("I" & dueValue), wsCopyHere.Range("H20:H" & dueValue), wsCopyHere.Range("K" & dueValue))
    .Font.Bold = True
    .Font.Italic = True
End With

谢谢干杯

标签： excelvb.netexcel-formula