count up the occurences of non continuous value

For your first requirement, you would need to count how many times the 'state' of the cell changes.

something like:

Select Case Current_State
       Case "ON"
            If New_State <> Current_State then
               Current_State = New_State
            End IF
       Case "OFF"
            If New_State = "ON" then
               On_Count = On_Count + 1
               Current_State = New_State
            End If
End Select

Embed this in a loop that starts at row 7 down to the last line of your spreadsheet.

As for your second request - I don't have the time at the mo - Sorry!

for your 2nd requirement the following loop will do the sum for you:

For each c in range("W7:W"&[w65536].End(xlUp).row)
if c.value= "ON" Then
s= s +c.offset.(-19,0)
end if
next

Thanks Rich_z and davidm,

Unfortunately I dont know a lot about vba and macros, and have to be a pain and ask to you show me how to put the code into a macro and make it work.

Do I need to add something else as well? I tried recording a macro, selecting the cell where I want the value to be put, then placed your code in the macro as well, but when I ran the macro, nothing appears to happen.

Can you please assist further,

Koala

Hi Koala,

It's not as simple as it seems.

Unfortunately to actually teach you what you need to know to do this would basically mean writing the stuff ourselves. I hate having to say this, but I work as a contract programmer and I'm answering these questions from work, and I really don't have the time to spend on that as my client would not get his work done!!

What I would suggest though is that if this is becoming part of your job, get your company to either send you on a VBA course or, get them to buy a book such as Wrox's Excell 2000 VBA (Or whatever version of Excel you are using) ISBN 1-861002-54-8. Use the Online help as well in VBA.

I'm really sorry I can't help you in full.

Regards

Rich

Thank you Rich,

I had a gut feeling that it would not be simple, otherwise everyone would know how to do it.

I am currently trying to learn VBA and have purchased a book titled "Excel 2002, Power Programming with VBA", by John Walkenbach.

Unfortunately trying to learn by reading books isnt always the best way to learn until you have mastered the basics and can fully understand what the author is trying to tell you.

Anyway thanks for the time you have taken, I am sure if I keep trying I will get something to work to make this task a bit easier.

cheers
Koala