fresh start/better explanation....totally stuck on loop code.pls h

[Sam]

Hi there,

You'll have to forgive me because i am a bit of a novice wiith respect to
vba but i am trying to learn it...rather painfully.

So here's the problem:

I have a column of data: example below:

COLUMN A COLUMN B
Last Price
1.64500
1.64520
1.64520
1.64540
1.64550
1.64550
1.64520
1.64570
1.64530
1.64510
1.64540
1.64550
1.64540
1.64540
1.64530
1.64500
1.64520
1.64560
1.64540
1.64580
1.64590
1.64570
1.64560
1.64550
1.64540
1.64520
1.64580
1.64580
1.64570
1.64600
1.64580
1.64600
1.64640
1.64650
1.64630
1.64650
1.64710
1.64710
1.64680
1.64690
1.64660
1.64650
1.64630
1.64640
1.64620
1.64640
1.64630
1.64620
1.64650
1.64650
1.64660
1.64640
1.64630
1.64640
1.64670
1.64690
1.64720
1.64720
1.64680
1.64680
1.64700



In "Column B" i want to do the following:

Step 1:

If the value in cell A2 is greater than or equal to the value in cell A5
assign value = 1
If the value in cell A2 is less than or equal to the value in cell A5 assign
value = -1

*at this point I realise that there is an illogical step (if the value is
the same, value according to the statement above will be equal to both 1 and
-1) step 2 explains what to do in this situation.

Step 2:

So we are currently calulating the relative 1's and -1’s as we descend the
column.
I now need to sum these cumulatively. See example below:

If i have 1,1,1,1,1 the formula needs to sum them as it "counts" > (showing
1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g.
1,1,1,1,-1,-1,-1,1,1,) it just starts the cumulative count again at zero >
(showing 1,2,3,4,-1,-2,-3,1,2). So:

Example 1: All positive 1’sâ€
(count) I would want the number to add to each other each time
(sum cumulatively)
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9

Example 2: All negative -1’sâ€
(count) I would want the number to add to each other each time
(sum cumulatively)
-1 -1
-1 -2
-1 -3
-1 -4
-1 -5
-1 -6
-1 -7
-1 -8
-1 -9

Example 3: IMPORTANT: IF POSITIVE AND NEGATIVE 1/-1’s
(count) In this situation the sum stops when the count changes
type (from -1 to 1, or 1 to -1) BUT the next count is EQUAL to the last one
the count continues. See example 4.
1 1
1 2
1 3
-1 -1
-1 -2
1 1
1 2
-1 -1
1 1

Example 4: I have included some of the relative movements that the 1’s and
-1’s are built off.
Raw data (count) I would want the number to add to each
other each time (sum cumulatively)
1.64500 1 1
1.64520 -1 -1
1.64520 -1 -2
1.64540 -1 -3 notice here that the two raw data values are EQUAL but we
still assign -1 and then add it in because it is part of the count. ONLY if
it were +1 would we stop the count.
1.64550 -1 -4
1.64550 1 1
1.64540 (changed to 40 just to show what I mean)
1.64570
1.64530

So what i would want the beginning bit of "Column B" to look like (I can’t
do it all manually)

Column A Column B
Raw data count sum count
1.64500 1 1
1.64520 -1 -1
1.64520 -1 -2
1.64540 1 1
1.64550 -1 -1
1.64550 1 1
1.64520 1 2
1.64570 1 3
1.64530 -1 -1
1.64510 -1 -2
1.64540 -1 -3
1.64550 1 1
1.64540 n/a n/a
1.64540 n/a n/a
1.64530 n/a n/a

Lastly,

If the count sums are negative, font = green, background = clear/normal
If the count sums are positive, font = red, background = clear/normal
If the count gets to + 9, cell = red, font = black bold
If the count gets to - 9, cell = green, font = black bold

basically i am totally stuck....i have tried writing some loops but mine
totally fail all the time....it's so so annoying. Sorry to ask for so much
help but i have been painfully staring at vba for dummies for the last week
and am stuck....


Thank you to anyone who can help me on this...

 

[ker_01]

Sam-

First, that was great of you to supply multiple examples. It was helpful in
trying to understand what you are looking to do. I think there is a simple
approach to get you what you want, although there are still a few missing
pieces.

I pasted your example data into column A. I then pasted this formula in B1:
IF(A1>=A2,1,IF(A1<A2,-1,0))
and copied the formula to the end of the data.
The only thing I'm not sure this fully addresses is your conflict between
whether a repeat value should end up as a +1 or -1, I didn't see a later
explanation on what your rules required.

Then I set C1 = 1, and in C2, I used this formula:
=IF(B2*C1>0,B2+C1,B2)
and copied it down.

Of course, you can combine all of this into one formula (all in column B) if
you wanted, but keeping it seperate makes it easier to troubleshoot.

I also didn't fully understand why your comparison was A2 vs A5; I used
sequential cells in these formulas, but you are welcome to edit as needed.

HTH,
Keith