offset question

[Guest]

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

 

[idyllicabyss]

Hmm...
I'm not clear on exactly what you want or which bit isn't working but
here goes.

Range(2,X+1) = v

Row reference then columnn reference. So row 2, Column X + 1.
This should give you range(2,2) throught to range(2,13)
which is columns B to M

rather than
Range(X&2) = v

the "&" character will either add the numbers together or attempt to
concatenate them into a string, I'm not sure without testing.

 

[Guest]

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

 

[idyllicabyss]

Hi Ram,

In your Offset formula you need to substitute the occurances of X that
relate to your X variable with
" & X & "
because you do not want the character X to appear, but it's value, eg
jan = "=SumProduct(--(Sheet4!A1:A30000=" & _
"OFFSET('processed Amount'!A1,0," & X & ",1,1)),--(Sheet4!AL1:AL30000

0))"

if it still doesn't work, try
" & cstr(X) & "

As for concatenating the value of X to the number 2, why? I don't think
it will work.
the Range command needs to be populated with a string, eg
Range("A5").value
I recommend using
Cells(2,X+1) = v
I was mistaken earlier when I said: Range(2,X+1) = v

 

[Guest]

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?

 

[Guest]

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

 

[ramone_johnson]

Thanks my code now works correctly

Hi Ram,

In your Offset formula you need to substitute the occurances of X that
relate to your X variable with
" & X & "
because you do not want the character X to appear, but it's value, eg
jan = "=SumProduct(--(Sheet4!A1:A30000=" & _
"OFFSET('processed Amount'!A1,0," & X & ",1,1)),--(Sheet4!AL1:AL30000
if it still doesn't work, try
" & cstr(X) & "

As for concatenating the value of X to the number 2, why? I don't think
it will work.
the Range command needs to be populated with a string, eg
Range("A5").value
I recommend using
Cells(2,X+1) = v
I was mistaken earlier when I said: Range(2,X+1) = v

 

[ramone_johnson]

Thanks for all your help

my code is now working correctly