talkmagic.co.uk

Ive got a bit of a pickle programming VBA for Excel

im sure its not a difficult problem but my knowledge is limited.

if anyone can help it'd be much appreciated, could you post here or pm and ill send my problem over.

ta

*waves*
Hi there

Though it's been many many years since I've done VB. But I'll try to help if I can. Though I probably can't.

thanks, i need a way of counting groups of entries. to explain its going to be used to monitor sickness in my work, so the amount of days is important (i can do that!) but so is the number of occasions.

a row will have a '1' if there has been a day off so the series

1
1
1
0
0
0
1
0
1
1

will be 3 as there were 3 occasions of sickness, 3 days, 1 day and 2 days.

i think the code will have to find the first one and then move cells until it finds a 0, adding one to a count and then displaying this at the end but im totally confused as to how to do it.

thanks for any help

Er... I'm thinking creating a function that will skip to the next instance of 0 which you call once you've hit a value of 1? Something like... (pseudocode, cos I can't remember VB syntax)

Code: Select all

function skipto0()
  repeat
    go_to_next_cell
  until $cellvalue == 0
end


I hope that makes sense...

cheers, ill have another go

I think another way to go is to not try to build an application. If you can do the calculation using worksheet functions you might well find that the spreadsheet becomes more portable (less security) and more maintainable (no source code).

what you seem to be asking for is an indication of the number of times the subset of numbers changes.

If your column (Ax:Ay) contains your set of values.

The total number of sick days is easy:
"=SUM(Ax:Ay)"

The total number of changes can be derived by using an adjacent column, or a column in another worksheet if you prefer. I'll use the adjacent column.
in each cell put this:
=IF(AND(An<>A(n-1),Bn=0),1,0)
where An is the cell to the left and A(n-1) is the cell northwest.
then put a zero as the first item in column A and the last item in column A so you have an interface to count. put the expression at the bottom of column B (ie at cell y+1) and sum column B
=SUM(B(x-1):b(y+1))

So your worksheet looks like this (assume the first row is 1):

Code: Select all

Column A Column B
___________________
1 |                    0
2 |1                 =IF(AND(A2<>A(1),B2=0),1,0)
3 |1                  ...
4 |1
5 |0
6 |0
7 |0
8 |1
9 |0
10|1
11|1
12|                   0
13|=sum(A)      =SUM(B)