Help converting worksheet formula to VBA

[Nogslaw]

I have the following array formula that works properly in my worksheet:

{=SUMPRODUCT(IF(C14:P14,(C13:P13/C14:P14)/($C$1:$P$1),""),(C14:P14))/SUM(C14:P14)}

There are 2 problems with having this formula in the worksheet. First, it will be copied down a column hundreds of times. This will cause very slow processing with all of these array formulas in the sheet. Second, once the value is calculated, the result should remain static, even though some of the reference values may change in the future. So, I was going to place the formula in the middle of some other code and simply paste the result in the appropriate cell, but kept getting errors when I tried to convert the formula into VBA.

I thought I had it with this, but I get a 'type mismatch error'

ProdPercent = WorksheetFunction.SumProduct((Range("C13:P13") / Range("C14:P14")) / Range("C1:P1"), Range("C14:P14")) / WorksheetFunction.Sum(Range("C14:P14"))

 

[Nogslaw]

I've realized I can shorten the formula, but still get a 'type mismatch' error in my code. This formula gives accurate results in the worksheet.

formula in worksheet: =SUMPRODUCT(C13:P13/C1:P1)/SUMC14:P14)

code in VB: ProdPercent = WorksheetFunction.SumProduct(Range("C13:P13") / Range("C1:P1")) / WorksheetFunction.Sum(Range("C14:P14"))

 

[Richard Schollar]

Hi Nogslaw

I couldn't get it to work thru using the Application.WorkSheetFunction method (it doesn't seem to like the divisor ("/") operator in the arguments to SUMPRODUCT) but I was able to get the calculation via:

ProdPercent = Evaluate("=SUMPRODUCT(C13:P13/C1:P1)/SUM(C14:P14)")

The argument to the Evaluate method is a string, so you can build that up how you like (ie with variables).

I hope this will be of some use to you.

Best regards

Richard

 

[Nogslaw]

Problem solved. Not the most efficient, I'm sure, but it works:

.Range(LR).Offset(0, 16).Formula = "=sumproduct(C" & Range _
     (LR).Row & ":P" & Range(LR).Row & "/C1:P1)/sum(C" & Range(LR).Row + 1 _
     & ":P" & Range(LR).Row + 1 & ")"
.Range(LR).Offset(0, 16).Value = .Range(LR).Offset(0, 16).Value

I have adapted my exact formula to be an adjustable variable so that when it pastes the results of the other cells, it adjusts to the proper row for calculation. Basically the code just pasted the formula in the cell, then copied and pasted the value in it's place after it was done.

 

[Norie]

How had you declared ProdPercent?

 

[Nogslaw]

I have trouble declaring variables, and that may have been one of my problems. I had tried declaring as long, and when that didn't work, I tried to just declare without specifying (I do this sometimes, try to see the result, and then pick), but that didn't work either. I don't understand some of the other ones.

 

[Norie]

Well Long actually means long integer, so that would have caused a type mismatch error.

I would have thought that not actually declaring the type would have worked OK. What problem did you have when you did that?

 

[Nogslaw]

Received a 'type mismatch' error with both.

 

[Norie]

Ah I now see why that is.

It's because of code like this.

(Range("C13:P13") / Range("C14:P14")

That might make sense on an Excel worksheet but it doesn't in VBA.

Did you ever try Richard's suggestion of using Evaluate/

 

[Nogslaw]

Yes, and it worked to get an array of string results for each of the corresponding reference cells, but I didn't know how to then combine those string results into my answer.