Very complex Loop
 

I need to loop thru €œB1:B100€� to place a number in each cell in that range
following this criterion: in €œA1:A100€�, every time the value changes, I need
to count the total occurrences of that value and divide 1 by that number (of
occurrences) then place that result in each correspondent cell in column B
then move to the next unique value and do the same. Obviously the data in
column A is sorted in order to avoid duplicates.


Thanks in advance

Very complex Loop
 

Hi,

If i've understood correctly this does what you want. Right click your sheet
tab, view code and paste this in and run it

Sub human()
Set myrange = Range("A2:A100")
For Each c In myrange
Count = Count + 1
If c.Value < c.Offset(-1, 0).Value Then
c.Offset(-1, 1).Value = 1 / Count
Count = 0
End If
Next
End Sub

Mike

"LuisE" wrote:

I need to loop thru €œB1:B100€� to place a number in each cell in that range
following this criterion: in €œA1:A100€�, every time the value changes, I need
to count the total occurrences of that value and divide 1 by that number (of
occurrences) then place that result in each correspondent cell in column B
then move to the next unique value and do the same. Obviously the data in
column A is sorted in order to avoid duplicates.


Thanks in advance

Very complex Loop
 

What do you mean by

to count the total occurrences of that value
and then I assume that you want the reciprocal of that number of
occurrences?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"LuisE" wrote in message
...
I need to loop thru "B1:B100" to place a number in each cell in that range
following this criterion: in "A1:A100", every time the value changes, I
need
to count the total occurrences of that value and divide 1 by that number
(of
occurrences) then place that result in each correspondent cell in column B
then move to the next unique value and do the same. Obviously the data in
column A is sorted in order to avoid duplicates.


Thanks in advance

Very complex Loop
 

Thanks Sandy.

Let's say that in the range the enrty "Florida" is repeated 4 times, then I
want to put 0.25 in each coreesponding cell in column B, 0.333 if it was
repeated only 3 times.



"Sandy Mann" wrote:

What do you mean by

to count the total occurrences of that value
and then I assume that you want the reciprocal of that number of
occurrences?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"LuisE" wrote in message
...
I need to loop thru "B1:B100" to place a number in each cell in that range
following this criterion: in "A1:A100", every time the value changes, I
need
to count the total occurrences of that value and divide 1 by that number
(of
occurrences) then place that result in each correspondent cell in column B
then move to the next unique value and do the same. Obviously the data in
column A is sorted in order to avoid duplicates.


Thanks in advance

Very complex Loop
 

You don't need VBA to do that, you can do it with formulas:

=IF(A1="","",1/(COUNTIF($A$1:$A$100,A1)))

In B1 and dragged down on the fill handle will do it.

If you are *never* going to have any blank cells then you only need:

=1/(COUNTIF($A$1:$A$100,A1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"LuisE" wrote in message
...
Thanks Sandy.

Let's say that in the range the enrty "Florida" is repeated 4 times, then
I
want to put 0.25 in each coreesponding cell in column B, 0.333 if it was
repeated only 3 times.



"Sandy Mann" wrote:

What do you mean by

to count the total occurrences of that value
and then I assume that you want the reciprocal of that number of
occurrences?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"LuisE" wrote in message
...
I need to loop thru "B1:B100" to place a number in each cell in that
range
following this criterion: in "A1:A100", every time the value changes, I
need
to count the total occurrences of that value and divide 1 by that
number
(of
occurrences) then place that result in each correspondent cell in
column B
then move to the next unique value and do the same. Obviously the data
in
column A is sorted in order to avoid duplicates.


Thanks in advance