Thanks, this is very helpful
Martin Fishlock wrote:
> Hi Paul:
>
> As far as I know you can't use array type formula in vba so you have to test
> each element seperately.
>
> Look at the code below:
>
> Option Explicit
>
> Function Purch_calc(look1 As String, look2 As String) As Integer
>
> Const l1_offset As Integer = -1 ' Set l1_range = .Range("d1:d10")
> Const l2_offset As Integer = -3 ' Set l2_range = .Range("b1:b10")
>
> Dim s_range As Range
> Dim rCell As Range
>
> Application.Volatile ' update on changes
>
> Set s_range = ActiveWorkbook.Worksheets("sheet6").Range("e1:e10")
>
> For Each rCell In s_range
> If (rCell.Offset(0, l1_offset) = look1) And _
> (rCell.Offset(0, l2_offset) = look2) Then
> Purch_calc = Purch_calc + rCell.Value
> End If
> Next rCell
>
> End Function
>
> --
> Hope this helps
> Martin Fishlock, Bangkok, Thailand
> Please do not forget to rate this reply.
>
>
> "halibut" wrote:
>
> > I am trying to create a function that sums a worksheet column based on
> > criteria that reside in adjacent columns. I used the following code
> > which gave me a type mismatch:
> >
> > Function Purch_calc(look1 As String, look2 As String) As Single
> >
> > Dim l1_range As Range
> > Dim l2_range As Range
> > Dim s_range As Range
> >
> > With Sheet6
> > Set l1_range = .Range("d1:d200")
> > Set l2_range = .Range("b1:b200")
> > Set s_range = .Range("e1:e200")
> > End With
> >
> > Purch_calc = Application.WorksheetFunction.SumProduct((l1_range =
> > look1) * (l2_range = look2), s_range)
> >
> > End Function
> >
> > Can anyone help me get the above to work or provide an alternative
> > method of summing a column using multiple criteria.
> >
> >
> > Thanks
> > Paul
> >
> >
|
Similar Threads
