VBA to automatically allocate a new person to an account

[Chaz-13]

Hi all

I need help with some coding - i have attached a dummy version of my report with name changes

I have produced a spreadsheet which the main function is to automatically allocate a new account manager to an account but this cannot be the last account manager, it has to be someone different.

I have set the spreadsheet up using formulas to randomly select a person from the individual teams - this all works perfectly.

What I am having trouble with is making it recognise the last account manager so when it radomly selects a new person, it is a different person to the last account manager.

I have probably set this up in quite a long way, but it works, I am just stuck with this last bit.

I have recorded a macro as a button which says "allocate now".

What I have been trying to do is, I have set up a formlua in the end column which picks up if the new account manager is the same as the old account manager it displays "yes". I wanted to add on additional VBA to the macro, so when I press "allocate now" it will radomly allocate an acocunt manger, scroll down the last collumn and if any display "yes" it will re-calcuate this collumn.

This is the code i have so far (which does not work):-

Sub Button1_Click()

Dim a As Integer
Dim x As Integer
Dim y As String
Dim z As String

x = 8
a = 9

Do Until x = 2000

ActiveCell = yes

y = "O" & CStr(x)
z = "O" & CStr(a)


Range(y).Select
ActiveCell.FormulaR1C1 = "=RC9&RANDBETWEEN(1,COUNTIF(TEAMS!R1C2:R260C2,RC9))"
Range(z).Select

x = x + 1
a = a + 1

Loop

x = 0
a = 0
x = 8
a = 9
z = ""

Do Until x = 2000

Range("P8:P800").Select
ActiveCell = yes

y = "P" & CStr(x)
z = "O" & CStr(a)

If Range(y).Value = "yes" Then

Range(z).Select
ActiveCell.FormulaR1C1 = "=RC9&RANDBETWEEN(1,COUNTIF(TEAMS!R1C2:R260C2,RC9))"

End If

x = x + 1
a = a + 1

Loop


End Sub



Hope that all makes sense - any chance of some help??

Many thanks!