How to search for a specific cells and change thei

Question

I am new to VBA in Excel. I was asked to make a distribution system to distribute customers to agents. I have a table contains Agents, rank and assigned total. The user select the city from user form and based on the selection the customer is assigned to the agent that has the smallest rank. So if user select London system search for agent that has smallest rank in London and then increase number of assign by 1 for that agent.

I try to write the code but am facing run time error, I know that I have a lot of mistakes in the if then part. The comment line I did know how to write the right code to apply the action I want.

Sub DistSystem()

Dim count As Integer
Dim City As String
Dim minmum As Long
Dim i As Integer

Dim array_rank() As Variant
Dim array_city() As Variant
Dim array_assign() As Variant

count = Sheets("111").Range("Y2").Value
City = Sheets("111").Range("W2").Value

array_city = Range("A1:A" & count).Value
array_rank = Range("E1:E" & count).Value
array_assign = Range("F1:F" & count).Value       

minmum = 1000000
i = 1

Do

    If City = array_city(i) And array_rank(i) <= minmum Then
        minmum = array_rank(i)    
    Else
        i = i + 1    
    End If

Loop While i <= count

MsgBox (minmum)

i = 1

Do

    If City = array_city(i) And minmum = array_rank(i) And Then

    'Here I want to apply an action: seting the new value by 
    ' incresing the assign number for the smallest rank

        array_assign(i) = array_assign(i) + 1

    Else    
        i = i + 1    
    End If

Loop While i <= count

End Sub

Answer 1

Your problem is with If City = array_city(i) And array_rank(i) <= minmum Then

Because you are reading the entire range into an array as you are, the resulting array is 2 dimensions, so to reference the values, you need to have both dimensions.

The reason you are getting the error is array_city(i) and array_rank(i) simply do not exist, so you end up with a "Runtime Error:Subscript of our range."

To quote myself:

The resulting array with be 2 dimensions, with the Row value as the first dimension and the column as the 2nd dimension. since all of the ranges are a single column, you would access any value by calling array_rank(Row,1) or array_city(Row,1) or array_assign(Row,1).

And as aucuparia astutely points out, your i = i + 1 needs to be outside of the if statement, otherwise, once you get to an i value that meets the first condition, i will never increment again and you'll be stuck in an infinite loop.

The if statement should read:

If City = array_city(i,1) And array_rank(i,1) <= minmum Then
    minmum = array_rank(i,1)    
End If
i = i + 1

Likewise, anywhere else you are referencing array_city, array_rank and array_assign you need to use the same syntax.