Help converting worksheet formula to VBA

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
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'

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

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
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

Well-known Member
Joined
Jul 9, 2004
Messages
904
Problem solved. Not the most efficient, I'm sure, but it works:

Code:
.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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How had you declared ProdPercent?
 

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
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-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Ah I now see why that is.

It's because of code like this.

Code:
(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

Well-known Member
Joined
Jul 9, 2004
Messages
904
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,389
Members
412,590
Latest member
Velly
Top