Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct basics
I'm trying to get started with User-Defined Functions. For the sake
of learning, I want to create a UDF that acts same as the built-in SumProduct function. So, the two arguments would be two ranges, range1 and range2. The first cell.value in range1 would be multiplied with the first cell.value in range2 and so on - and then all added together at the end. I thought I'd start by using a for loop to go through each cell in range1 but now I'm not sure what is the best/most efficient method to pair the values in range1 with their corresponding values in range2. How should this be done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct basics
First, this would work if the ranges passed are identical in size and each
consists of a single row (or column). Maybe better: At least one dimension of each range has to be 1--you can't have 23 rows by 2 columns. (This isn't a limitation for =sumproduct(), though.) The first thing I would try is to determine the dimensions of the second passed range. If it's a vertical range, you could just loop through the first range and use ..offset(x,0) to process the second. If it's horizontal, you could loop through the first and use .offset(0,x) for the second. So assuming the easiest scenario... Option Explicit Function mySP(rng1 As Range, rng2 As Range) As Variant Dim Rng2IsVertical As Boolean Dim myCell As Range Dim myTotal As Double Dim myOffset As Long Dim Rng2Cell As Range If rng1.Areas.Count 1 _ Or rng2.Areas.Count 1 Then mySP = "Only single areas for each range!" Exit Function End If If rng1.Columns.Count 1 _ And rng1.Rows.Count 1 Then mySP = "First range is too big!" Exit Function End If If rng2.Columns.Count 1 _ And rng2.Rows.Count 1 Then mySP = "Second range is too big!" Exit Function End If If rng1.Cells.Count = rng2.Cells.Count Then 'ok, keep going Else mySP = "Cell count doesn't match" Exit Function End If If rng2.Rows.Count 1 Then Rng2IsVertical = True 'like A1:A10 Else Rng2IsVertical = False 'like A1:K1 End If myTotal = 0 myOffset = 0 'start in the first cell of rng2 For Each myCell In rng1.Cells If Application.IsNumber(myCell.Value) Then If Rng2IsVertical Then Set Rng2Cell = rng2.Cells(1).Offset(myOffset, 0) Else Set Rng2Cell = rng2.Cells(1).Offset(0, myOffset) End If If Application.IsNumber(Rng2Cell.Value) Then myTotal = myTotal + (myCell.Value * Rng2Cell.Value) End If 'get ready for the next cell myOffset = myOffset + 1 End If Next myCell mySP = myTotal End Function MLT wrote: I'm trying to get started with User-Defined Functions. For the sake of learning, I want to create a UDF that acts same as the built-in SumProduct function. So, the two arguments would be two ranges, range1 and range2. The first cell.value in range1 would be multiplied with the first cell.value in range2 and so on - and then all added together at the end. I thought I'd start by using a for loop to go through each cell in range1 but now I'm not sure what is the best/most efficient method to pair the values in range1 with their corresponding values in range2. How should this be done? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct basics
Great! Now how about this: I have a user-defined one-dimensional
range (say A1:A100). How do I ask for the value of the nth element in that range? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct basics
=index(a1:a100,n)
in code: dim myVal as variant dim n as long n = 66 myval = application.index(worksheets("Somesheetname").rang e("a1:a100"), n) MLT wrote: Great! Now how about this: I have a user-defined one-dimensional range (say A1:A100). How do I ask for the value of the nth element in that range? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PDF basics | Excel Programming | |||
Add Ins basics | Excel Programming | |||
basics? | New Users to Excel | |||
MsgBox basics | Excel Programming | |||
Help with the basics. Starting out. | Excel Programming |