Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Worksheetfunction.Sumproduct
Hi all,
I have got this groovy formula below as an Excel formula and would like to make it usable as VBA: =SUMPRODUCT(--(B3:B16-B2:B15=0),B3:B16-B2:B15) I realised this "-" in the formula means "multiply by -1". I tried: Dim v As Variant v = Application.WorksheetFunction.SumProduct(--(Range("B3:b16") - Range("b2:b15") = 0), Range("B3:b16") - Range("b2:b15")) But I get Type Mismatch error... Can anyone help? Thanks in advance, Tetsuya |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Worksheetfunction.Sumproduct
v = Activesheet.Evaluate("SUMPRODUCT(--(B3:B16-B2:B15=0),B3:B16-B2:B15)")
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tetsuya Oguma" wrote in message ... Hi all, I have got this groovy formula below as an Excel formula and would like to make it usable as VBA: =SUMPRODUCT(--(B3:B16-B2:B15=0),B3:B16-B2:B15) I realised this "-" in the formula means "multiply by -1". I tried: Dim v As Variant v = Application.WorksheetFunction.SumProduct(--(Range("B3:b16") - Range("b2:b15") = 0), Range("B3:b16") - Range("b2:b15")) But I get Type Mismatch error... Can anyone help? Thanks in advance, Tetsuya |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Worksheetfunction.Sumproduct
Will this do?
Total = 0 for each cell in Range("B3:B16") if cell = cell.offset(-1,0) then total = total + (cell - cell.offset(-1,0)) end if next cell The "--" really converts the TRUE's to 1 and the False's to 0. so instead of getting an array of {true, false, false, True,...) You get {1,0,0,1,...} Sumproduct doesn't know how to multiply {true * 1}. the -- changes this to {1 * 1} "Tetsuya Oguma" wrote: Hi all, I have got this groovy formula below as an Excel formula and would like to make it usable as VBA: =SUMPRODUCT(--(B3:B16-B2:B15=0),B3:B16-B2:B15) I realised this "-" in the formula means "multiply by -1". I tried: Dim v As Variant v = Application.WorksheetFunction.SumProduct(--(Range("B3:b16") - Range("b2:b15") = 0), Range("B3:b16") - Range("b2:b15")) But I get Type Mismatch error... Can anyone help? Thanks in advance, Tetsuya |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Worksheetfunction.Sumproduct
"Joel" wrote in message ... Sumproduct doesn't know how to multiply {true * 1}. the -- changes this to {1 * 1} =SUMPRODUCT(({TRUE,TRUE})*({1,1})) =2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Worksheetfunction.Sumproduct
Just to add to the information
You can use sumproduct for its original intended purpose of multiplying two or more arrays element by element and summing the results using application.Sumproduct. However your "groovy" formula is using the special ability of sumproduct in a worksheet to evaluate it arguments as array formulas. This ability is not supported in VBA. the Evaluate function in VBA can interpret a worksheet formula supplied as an argument as if it were in a virtual cell. thus Bob has suggested that use which does support the special abilities of sumproduct to act as an array formula. -- Regards, Tom Ogilvy "Tetsuya Oguma" wrote: Hi all, I have got this groovy formula below as an Excel formula and would like to make it usable as VBA: =SUMPRODUCT(--(B3:B16-B2:B15=0),B3:B16-B2:B15) I realised this "-" in the formula means "multiply by -1". I tried: Dim v As Variant v = Application.WorksheetFunction.SumProduct(--(Range("B3:b16") - Range("b2:b15") = 0), Range("B3:b16") - Range("b2:b15")) But I get Type Mismatch error... Can anyone help? Thanks in advance, Tetsuya |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with Application.WorksheetFunction | Excel Discussion (Misc queries) | |||
Application.WorksheetFunction.VLookup (Plz Help) | Excel Programming | |||
application.worksheetfunction | Excel Programming | |||
Application.worksheetfunction | Excel Programming | |||
Using Application.WorksheetFunction.Ln(...) in VBA | Excel Programming |