JL,

One more thing to add to my previous communication. As the procedure
drops down to the next cell and begins calc for the next I3 at X1 :

For Count 0 to 3-1:

Position (count) 0 = .07
Position (count) 1= .06
Position (count) 2 = .04

I3=.17 A coincidence with the previous I3

In addition the only math that I am interested in is the sum of the
Q3's over the varying counts. The math for column AH is already
performed and its intger taken. The Q3's automatically change as the
spreadsheet is updated. In that event I would have to run the count
procedure to update the I3's.

LS

JLatham wrote:
So SP/2 is a descriptive title, not a math operation. That is, it does not
mean take the value in the column and divide that by 2, correct?

To further confirm this, in your initial example, where we have .06 x and 4,
what would you expect to get as the value for x in column AG? Which numbers
would it use from AF, same for the next line where we have .07, x1 and 3? I
think with that information we can fine tune the For LC = loop, which I think
is where we are probably hung up right now.

As it is written now, for the first value of 4, the code takes the 4 and
divides it by 2, giving 2 and subtracts 1 from that, with a result of 1. So
the loop counts from 0 to 1 and would get the .06 and .04 values.

But if we are to simply take the value 4 and subtract 1 from that, then the
loop would go from 0 to 3 and get values .06, .04, .05 and .02 to give a
result of .17 for I3 at that point.

As for the cells not containing blanks from start to end, that's great - but
the code can stand as is just in case. Depending on the number of data
points to be evaluated, it might add a little time to the processing, but
doesn't affect things otherwise. But if you are absolutely certain of no
empty cells in AH from start of data (AH2) until the end, then you could
remove the

If Not(IsEmpty(ActiveCell)) Then

and

End If

lines of code from the example, leaving the rest in place.

The code is/will do as you desired as far as moving one by one down through
the entries in AH (SP/2) and examining each one of them and doing some math
based on the value found there. The only question is exactly what math is to
be done, and that's what goes on inside of the For LC = loop.

"LenS" wrote:

JL

I'm sorry for the confusion, I should take more time to detail the
problem.

As for the value in AH, in my original communication:

There are no blanks in this column and how this value is used is as a
counting value (as per the Easy language code : For Count= 0 to
Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3
value to I3 which is initially =0. I think that's what the Easy Lang.
code is saying but I'm not sure. When this counter SP/2 is done, 4 in
this part, I will have obtained I3 for the current cell in AG. I then
want the routine to drop to the next cell in AG and begin the process
again with a new counter (SP/2 is 3 in the next case) and calculate I3
for that cell. So column AH determines how many Q3's are involved at
any one run.

I apologize again for not being clear. I'm sure you run into alot of
that programmer vs non-programmer. I really appreciate the time you put
into this, thanks.

Keep in mind that column AF and AH have no blanks except at the
beginning and end of the data.

LenS


JLatham (removethis) wrote:
I'll try to help some, but I must admit to being confused by how you want the
value in AH to be used to figure out which values of Q3 (In col AF) to add
together to get the value for I3 in column AG. So the code may not be doing
that properly. First the code, then some explanation.

Sub CalculateI3()
Dim I3value As Single
Dim LastRowOfData as Long
Dim LC as Integer ' Loop Counter

'find last row with data in column AH
LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row
'go to first possible data entry in AH
'assumes row 1 has header text
Range("AH2").Select
'work down thru all cells to last row used
Do While ActiveCell.Row <= LastRowOfData
'assumes if cell in AH is not empty, it is number
If Not(IsEmpty(ActiveCell)) Then
'I'm a little confused here, so may not be right
For LC = 0 to Int(ActiveCell.Value/2)-1
ActiveCell.Offset(0,-1) = _
ActiveCell.Offset(0, -1) + _
ActiveCell.Offset(-LC,-2)
Next ' end of LC loop
End If ' test for empty cells
'move to next Row
ActiveCell.Offset(1, 0).Activate
Loop ' down thru rows
End Sub

The 'Dim' statements declare a variable for use later, simply reserving room
for it to be used. If your code module includes the declaration
Option Explicit
at the beginning of it, then Dim or Const declarations are required. I
recommend it because of several reasons that I won't go into here (end
result: better code less prone to failure).

Because we are going to be doing the math based on the contents of cells, we
don't need variables to hold interim results. But we do need to know how far
down the sheet to work at getting information and we need to know how many
values of Q3 to add together to get the I3 values.

LastRowOfData is set up as type Long so it can hold very large integer
numbers, since you may have thousands of rows of data to work through. LC,
to be used as a loop counter, is set up as Integer to hold smaller integer
numbers, although to be on the safe side it possibly should be set up as type
Long also.

The first executable line of code looks up from the bottom of column AH
until it finds some entry in that column. That tells us how far down the
sheet we have to work in examining the information to be used. We save that
row number for reference in LastRowOfData.

We get into a Do While loop that is simply going to work down the SP2 column
row by row looking for numbers until it gets past the LastRowOfData on the
sheet.

the If Not(IsEmpty()) statement is used to test if there's something in a
cell in the SP2 column (AH) and if there is, an assumption that it is a
number is made and we then calculate the I3 value.

A little about the ActiveCell.Offset() instructions. Offset() takes two
arguments, first the number of rows to offset, where negative numbers are
"up" the sheet and positive numbers are on "down" the sheet, and zero is same
row the active cell is on. Second argument is the number of columns to
offset from the location; negative numbers are to the left of the reference
cell, positive numbers are to the right of it. Zero is in same column. So
an .Offset(0, 0) is actually no offset at all, and would refer to the
reference location itself.

The LC loop: is based on the value in SP2 (column AH) at the current time.
The way this loop is set up it will always add at least one number to get the
I3 value - the value for Q3 on the same row. The math is actually done as a
single formula, the " _" at the end of a line tells VB that the line
continues on the next line also.

ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for
the current cell in AH (SP2) being examined. This formula literally says
"take the value in the cell in column AG and replace that value with the
value calculated by taking its current contents and adding another value to
it". That "another value" is obtained by using the LC value as an offset
pointer over into column AF (Q3) as the row pointer. We put a - symbol in
front of the reference to LC in the Offset statement to tell it to look "up"
the sheet rather than down farther on the sheet.

So when SP2 is 4, we would add 2 values together to come up with I3:
4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using
offset values of 0, and 1, meaning we would add .04 and .06 together to come
up with a value of .10 for I3 at that point.

I hope this helps you get started on this. If you need more explanation,
just ask. There are other ways to do this, but this one is, I think, the
most straight-forward and most 'visible' to you at this point. I'll keep an
eye here for responses (questions) and I can always be reached at HelpFrom @
jlathamsite.com (remove spaces) if the discussion needs to go in some
direction that is best handled off-line, but questions and answers here will
eventually serve the community better if possible.

"LenS" wrote:

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