Thread: Update numbers
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] jan120253@gmail.com is offline
external usenet poster
 
Posts: 40
Default Update numbers

I have in principle two colums A and B. In column A I have continuous numbers, from 1 to 500. In Column B I havce names. Something like

1 Brian
2 James
3 Maria
4 Victoria
5 Henning
6 Rita
..
..
..

ans so on. What I like is to be able to type a new number for one of the names, and have the rest of the numbers change automatically in accordance, like:

If I type 2 next to Rita, the number next to James should change to 3, maria 4, and so on.

next the list must be sorted in the new order, which already works fine, but I can't get the autoreumbering to work.

I try to use the Worksheet_Change event onthe A-column, ad i does change the numbers, but unfortunately these changes triggers the events once again, and that is not the idea. I ha ve tird usen the C column to type the new number, iike

No. Name
1 Brian
2 James
3 Maria
4 Victoria
5 Henning
6 Rita 2

and use the Worksheet_Change event on that column. In my code I have something like

If Not Intersect(Target, Range("C2:C501) Is Nothing Then
For Each c in Range(A2:A501).Cells
If c.Value < target.Value then
c.Value = c.Value + 0
Elseif c.Value Target.Value
c.value = c.Value + 0
Else
c.Value = c.Value + 1
End If
Next
End if

but I can't get it to work as it creates a list like this

0 Brian
3 James
4 Maria
5 Victoria
6 Henning
3 Rita

and the following nunmbers are chanbged as well even if no number seven or higher should change.

any ideas on how to accomplish what i want?

Jan