Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset question
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset question
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset question
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset question
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset question
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset question
Thanks for all your help
my code is now working correctly 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset Question | Excel Worksheet Functions | |||
Offset question | Excel Worksheet Functions | |||
OFFSET() question for '97 | Excel Discussion (Misc queries) | |||
Offset question | Excel Programming | |||
An OFFSET question | Excel Worksheet Functions |