|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 answer6141 于 2020-1-12 23:49 编辑
- Option Explicit
- Sub test()
- Dim i, j, m As Integer
- Dim dic As Object
- Dim arr, drr
- Set dic = CreateObject("scripting.dictionary")
-
- With Sheets("省份区域划分")
- For i = 2 To 35
- dic(Trim(.Range("b" & i))) = Trim(.Range("c" & i))
- Next
- drr = dic.keys
-
- End With
- With Sheets(1)
- arr = .Range("a2:j" & .Cells(.Rows.Count, 1).End(xlUp).Row)
- For j = 1 To UBound(arr)
- Select Case dic(arr(j, 7))
- '全是按一区写的,没改
- Case Is = "一区"
- Select Case Trim(arr(j, 9))
- Case Is <= 3
- arr(j, 10) = 6
- Case Is <= 4
- arr(j, 10) = 9
- Case Is <= 5
- arr(j, 10) = 11
- Case Else
- arr(j, 10) = Round((arr(j, 9) - 3), 0) * 1 + 6
- End Select
- Case Is = "二区"
- Select Case Trim(arr(j, 9))
- Case Is <= 3
- arr(j, 10) = 6
- Case Is <= 4
- arr(j, 10) = 9
- Case Is <= 5
- arr(j, 10) = 11
- Case Else
- arr(j, 10) = Round((arr(j, 9) - 3), 0) * 1 + 6
- End Select
- Case Is = "三区"
- Select Case Trim(arr(j, 9))
- Case Is <= 3
- arr(j, 10) = 6
- Case Is <= 4
- arr(j, 10) = 9
- Case Is <= 5
- arr(j, 10) = 11
- Case Else
- arr(j, 10) = Round((arr(j, 9) - 3), 0) * 1 + 6
- End Select
- Case Is = "四区"
- Select Case Trim(arr(j, 9))
- Case Is <= 3
- arr(j, 10) = 6
- Case Is <= 4
- arr(j, 10) = 9
- Case Is <= 5
- arr(j, 10) = 11
- Case Else
- arr(j, 10) = Round((arr(j, 9) - 3), 0) * 1 + 6
- End Select
- End Select
- Next
- .Range("a2:j" & .Cells(.Rows.Count, 1).End(xlUp).Row) = arr
- End With
- End Sub
复制代码 |
-
|