-5

I'm trying to basically translate a formula from excel into VBA. I'm using the SUMIFS() command and I have it referring to columns in an already established table. This is what I have so far but I keep getting an object error.

Cells(2, 14).Value = 60 * Application.WorksheetFunction.SumIfs([Hours],[Operators], [@Operators], [Forging], [@Forging], [Operation], [@Operation], [Job No], [@[Job No]], [Time], ">" & [Time] - Time(1, 0, 0), [Time], "<" & [Time] + Time(1, 0, 0), [Workcenter], [@Workcenter])

I'm guessing that this isn't how you use SUMIFS() so how do you modify this so that it can function as it does in excel.

Any help is appreciated!

PMac
  • 11
  • 4

1 Answers1

1

It's difficult to exploit the power of the structured formula from VBA. Since you have a tested and working formula, you can let the cell evaluate it as is and then fix the value. for example:

With sheet1.Cells(2, 14)
  .Formula = "=SUMIFS([Hours],[Operators], [@Operators], [Forging], [@Forging], [Operation], [@Operation], [Job No], [@[Job No]], [Time], "">"" & [Time] - Time(1, 0, 0), [Time], ""<"" & [Time] + Time(1, 0, 0), [Workcenter], [@Workcenter])"
  .Value = .Value * 60
End with

only thing to be careful about here was: double-up any double-quotes inside the initial formula.

A.S.H
  • 29,101
  • 5
  • 23
  • 50