offset question

[ram]

could someone help me with this code

Private Sub Worksheet_Activate()

Dim jan As String, I As Long, X As Integer

Dim v As Variant

For I = 1 To 12
X = I
jan = "=SumProduct(--(Sheet4!A1:A30000=" & _
"OFFSET('processed Amount'!A1,0,X,1,1)),--(Sheet4!AL1:AL30000 > 0))"
v = Evaluate(jan)
Range(X&2) = v
Next I
End Sub

I'm trying to populate cells b2...m2 with the number of counts based on the
following criteria:
the date is equal to the dates in cells b1...m1 and the total is >0

Thanks for nay help

[ram]

it doesn't seem like the X in the offset procedure is working correctly in
cell B2 I have the #name? error message

at the end of the code i'm trying to concantenate the X variable and row 2.

Thanks for any help

"ram" wrote:

> could someone help me with this code
>
> Private Sub Worksheet_Activate()
>
> Dim jan As String, I As Long, X As Integer
>
> Dim v As Variant
>
> For I = 1 To 12
> X = I
> jan = "=SumProduct(--(Sheet4!A1:A30000=" & _
> "OFFSET('processed Amount'!A1,0,X,1,1)),--(Sheet4!AL1:AL30000 > 0))"
> v = Evaluate(jan)
> Range(X&2) = v
> Next I
> End Sub
>
> I'm trying to populate cells b2...m2 with the number of counts based on the
> following criteria:
> the date is equal to the dates in cells b1...m1 and the total is >0
>
> Thanks for nay help

[bpeltzer]

I think "range(X&2)=v" should be "cells(2,x+1)=v" (+1 because you suggested
populating columns b:m, but X takes on the values 1 through 12).
Why not just put the sumproduct formulas on the worksheet, rather than
calculate the formula results on activation?

"ram" wrote:

> it doesn't seem like the X in the offset procedure is working correctly in
> cell B2 I have the #name? error message
>
> at the end of the code i'm trying to concantenate the X variable and row 2.
>
> Thanks for any help
>
> "ram" wrote:
>
> > could someone help me with this code
> >
> > Private Sub Worksheet_Activate()
> >
> > Dim jan As String, I As Long, X As Integer
> >
> > Dim v As Variant
> >
> > For I = 1 To 12
> > X = I
> > jan = "=SumProduct(--(Sheet4!A1:A30000=" & _
> > "OFFSET('processed Amount'!A1,0,X,1,1)),--(Sheet4!AL1:AL30000 > 0))"
> > v = Evaluate(jan)
> > Range(X&2) = v
> > Next I
> > End Sub
> >
> > I'm trying to populate cells b2...m2 with the number of counts based on the
> > following criteria:
> > the date is equal to the dates in cells b1...m1 and the total is >0
> >
> > Thanks for nay help

[ram]

thanks forthe response

I changed the range(x&2)=v to cells(2,x=1)=v and now cells b2...m2 are
populated, however they all have the #name? error. Do you know why that would
happen?

is it because the word offset is in the wrong place?


Thanks for any help

"bpeltzer" wrote:

> I think "range(X&2)=v" should be "cells(2,x+1)=v" (+1 because you suggested
> populating columns b:m, but X takes on the values 1 through 12).
> Why not just put the sumproduct formulas on the worksheet, rather than
> calculate the formula results on activation?
>
> "ram" wrote:
>
> > it doesn't seem like the X in the offset procedure is working correctly in
> > cell B2 I have the #name? error message
> >
> > at the end of the code i'm trying to concantenate the X variable and row 2.
> >
> > Thanks for any help
> >
> > "ram" wrote:
> >
> > > could someone help me with this code
> > >
> > > Private Sub Worksheet_Activate()
> > >
> > > Dim jan As String, I As Long, X As Integer
> > >
> > > Dim v As Variant
> > >
> > > For I = 1 To 12
> > > X = I
> > > jan = "=SumProduct(--(Sheet4!A1:A30000=" & _
> > > "OFFSET('processed Amount'!A1,0,X,1,1)),--(Sheet4!AL1:AL30000 > 0))"
> > > v = Evaluate(jan)
> > > Range(X&2) = v
> > > Next I
> > > End Sub
> > >
> > > I'm trying to populate cells b2...m2 with the number of counts based on the
> > > following criteria:
> > > the date is equal to the dates in cells b1...m1 and the total is >0
> > >
> > > Thanks for nay help