FOR COUNT (changing ranges, repetitive calculations)

[LenSak]

Hello,

I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column.

A code I found in easy language looks like this:

For count= 0 to Int(SP/2) -1 begin
I3=I3 + Q3(Count)

My Excel 2000 spreadsheet looks like this:

Col: AF AG AH
(Q3) (I3) (SP/2)

.03
.02
.05
.04
.06 X 4
.07 X1 3
. . .
. . .
.01 Xn 6

I want to be able to calculate I3 for x, x1 and...... xn

How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it.

Thanks
LS

 

[Mikeb1970]

Hmm not sure i understand what you mean here, maybe you need to give us some more info

Mike

 

[SkipVought]

Hi,

Your problem is not stated very clearly.

Please post and ACTUAL EXAMPLE of data and what result would be expected from such.

Skip,
[sub]
[red][/red]
[/sub]

 

[LenSak]

Hello,

I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent. How many values of Q3 that I sum depends on what another value, SP/2 (division already performed) is in the last column.

A code I found in easy language looks like this:

For count= 0 to Int(SP/2) -1 begin
I3=I3 + Q3(Count)

My Excel 2000 spreadsheet looks like this:


Col: AF AG AH
(Q3) (I3) (SP/2)


.03
.02
.05
.04
.06 X 4
.07 X1 3
. . .
. . .
.01 Xn 6


I want to be able to calculate I3 for x, x1 and...... xn

SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement:


For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3:


Position (count) 0 where Q3=.06,
Position (count) 1 where Q3=.04,
Position (count) 2 where Q3=.05,
Position (count) 3 where Q3=.02.


Position 0 always refers to current days data, position 1 refers to the previous days data and so on.

If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02.


The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05.


The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04

The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17.
I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06.


When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the
whole data base is calculated for I3.


I hope that clears the ambiguity. Sorry about the non-clarity.


How do I do all this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it.

Thanks
LS

 

[SkipVought]

What is the business case for this problem?

Perhapse it would help if you would describe the problem in functional terms -- ie the business purpose.

Skip,
[sub]
[red][/red]
[/sub]