I am looking for some help on a VBA 1

[scevbact]

I am looking for some help on a VBA function that would do the following

1) I Have a table that has one of the fields that has multiple values (Field1). An adjoining field is called ranking and it has null values. Thus for instance the value 411 could be repeated 2, or 3 times in Field1
2) I want the Function, or Procedure to look at the first value in Field1 and assign a 10 in the Ranking field. If there are more than one record with the same value (411) it would go to the second record and assign an increment of 10. So the second record with the value 411 in Field1 would have 20.
3) If the third record is, say 412, the ranking filed would reset to 10 and follow the same logics as in (2) above.

Thanks for your help

 

[SkipVought]

Hi,

Here's my test table...
[pre]
Field1 Field2
411 10
411 20
411 30
412 10
412 20
413 10
414 10
414 20
414 30
414 40
[/pre]

Formula in B2...
[tt]
B2: =IF(A2=A1,B1+10,10)
[/tt]
...and copy/paste down.

So why use a program when a simple formula will suffice?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[scevbact]

Thanks Skip. That's the exact formula that I am using in EXCEL. However I want to emulate that function in Ms ACCESS. Any suggestions?

 

[SkipVought]

What have you tried?

BYW, you never mentioned Access. Access VBA is forum705.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]