Getting number of value changes in column with count of each durat

D

DRK

I have 42 columns each starting in Row 2 ending in Row 121.

I need to walk through each column and find each change in value and how
many rows between changes. Each row is a year.

For example, I have a column with these values
Row1 - Column name (so not part of the loop)
Rows 2:6 - value is 5.00 in each cell
Rows 7:11 - value is 2.50 in each cell
Rows 12:121 - value is 0.00 in each cell

What I need to produce in the end is a listing of each value and how many
rows for each value. So I'd end up with a listing like:
Value = 5.00 for 5 years
Value = 2.50 for 5 years
Value = 0.0 for 110 years

Some columns may change every year. Yes - the lawyers tell me I'd have to
list each year one by one.

One one hand this could be a For/Next loop since we know the number of rows
to examine. But then would a Do Until work better? Or use a Do Until inside
the For/Next?

Ideally I want a sub or function that can be used for all the columns. I can
pass the current column number in by argument.

Values can be doubles or integers or blanks. So I'm assuming I would use
Variant as the value type.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top