如何搜索特定的单元格并通过VBA更改其值 [英] How to search for a specific cells and change their value by VBA

问题描述

我是Excel中VBA的新手.我被要求建立一个分销系统，以将客户分配给代理商.我有一张表格，其中包含特工，排名和分配的总数.用户从用户表单中选择城市，然后根据选择将客户分配给排名最小的座席.因此，如果用户选择伦敦系统，则搜索伦敦排名最小的座席，然后将该座席的分配数量增加1.

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.

我尝试编写代码，但是遇到了运行时错误，我知道if if部分有很多错误.我确实知道注释行如何编写正确的代码以应用我想要的操作.

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

推荐答案

您的问题出在 If City = array_city(i)And array_rank(i)< = minmum then

因为您正按原样将整个范围读取到一个数组中，所以生成的数组是2维，因此要引用这些值，您需要具有两个维.

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.

出现错误的原因是 array_city(i)和 array_rank(i)根本不存在，因此最终出现运行时错误:下标我们的范围之内."

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."

要引用自己:

具有2个维度的结果数组，其中Row值是第一个维度，column列是第2个维度.由于所有范围都是一列，因此您可以通过调用array_rank(Row，1)或array_city(Row，1)或array_assign(Row，1)来访问任何值.

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).

正如 aucuparia 指出的那样，您的 i = i + 1 必须是否则，一旦获得满足第一个条件的 i 值， i 就永远不会再增加，并且您将陷入无限循环

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.

if语句应显示为:

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

同样，在您引用 array_city ， array_rank 和 array_assign 的其他任何地方，您都需要使用相同的语法.

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

这篇关于如何搜索特定的单元格并通过VBA更改其值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！