vba根据数组列字符串中的姓名“韩思航”,在另一个数组列里分组“器械组”
时间: 2023-06-10 21:08:52 浏览: 134
可以使用 VBA 的循环和字符串比较来实现该功能。以下是一个示例代码:
```vb
Sub GroupByName()
Dim nameArray As Variant
Dim groupArray As Variant
Dim i As Long, j As Long
'假设姓名数组和分组数组分别为以下两个列的数据
nameArray = Range("A2:A10").Value
groupArray = Range("B2:B10").Value
'循环遍历姓名数组,找到姓名为“韩思航”的行,并将其分组设置为“器械组”
For i = LBound(nameArray, 1) To UBound(nameArray, 1)
If nameArray(i, 1) = "韩思航" Then
groupArray(i, 1) = "器械组"
End If
Next i
'将更新后的分组数组写回到原始数据范围
Range("B2:B10").Value = groupArray
End Sub
```
在上面的代码中,我们首先将姓名数组和分组数组读入到变量中,然后使用一个循环遍历姓名数组,对于每一个姓名为“韩思航”的行,将其对应的分组设置为“器械组”。最后,将更新后的分组数组写回到原始数据范围。
相关问题
用VBA实现下面的业务:1. 对M列,从M6单元格开始,到最大的行数的单元格区间的每一个单元格,按字符/分割单元格内的字符串,形成字符数组arri, 2. 遍历数组arri,若数组元素的值在名为xxx工作簿的A列里,则设置该值为N列对应行的值,
下面是实现上述业务的VBA代码:
Sub SplitAndMatch()
Dim lastRow As Long
Dim i As Long, j As Long
Dim arri As Variant
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim arrA As Variant
Dim arrN As Variant
Dim found As Boolean
Set ws = ActiveSheet '或者指定要处理的工作表,比如Set ws = Worksheets("Sheet1")
Set ws1 = Workbooks("xxx.xlsx").Worksheets("Sheet1") '指定要匹配的工作簿和工作表
'获取M列最大行数
lastRow = ws.Cells(ws.Rows.Count, 13).End(xlUp).Row
'循环处理每一行
For i = 6 To lastRow
'按/分割M列单元格内字符串成数组
arri = Split(ws.Cells(i, 13).Value, "/")
'遍历数组arri
For j = LBound(arri) To UBound(arri)
'在xxx工作簿的A列中查找当前数组元素的值,找到则设置N列对应行的值
arrA = ws1.Range("A1", ws1.Cells(ws1.Rows.Count, 1).End(xlUp)).Value
arrN = ws.Range("N1", ws.Cells(ws.Rows.Count, 14).End(xlUp)).Value
found = False
For k = LBound(arrA) To UBound(arrA)
If arrA(k, 1) = arri(j) Then
arrN(i, 1) = arrA(k, 1)
found = True
Exit For
End If
Next k
If Not found Then
arrN(i, 1) = ""
End If
Next j
Next i
'将结果写入N列
ws.Range("N1", ws.Cells(lastRow, 14)).Value = arrN
End Sub
上述代码中,我们首先获取了M列最大行数,然后对于每一行,按/分割M列单元格内字符串成数组,然后遍历数组arri,按照在xxx工作簿的A列中查找当前数组元素的值的方式,找到则设置N列对应行的值,最后将结果写入N列。注意,上述代码中的xxx.xlsx和Sheet1需要根据实际情况进行修改。
用VBA实现下面的业务:1. 对M变量列,从MN(N为行变量)单元格开始,到最大的行数的单元格区间的每一个单元格,按字符变量c分割单元格内的字符串,形成字符数组arri, 2. 遍历数组arri,若数组元素的值在名为xxx工作簿的A列里,则设置该值为N列对应行的值,
Sub SeparateAndCheck()
Dim c As String '分割用的字符变量c
c = ","
Dim lastRow As Long '最大行数
lastRow = Cells(Rows.Count, "M").End(xlUp).Row
Dim i As Long '循环变量
For i = 13 To lastRow '从第13行开始循环,因为MN位置是第13行
Dim cellValue As String
cellValue = Cells(i, "M").Value '取出M列每个单元格的值
Dim arr() As String
arr = Split(cellValue, c) '按c字符分割值,生成数组
Dim j As Long '循环变量
For j = LBound(arr) To UBound(arr) '遍历数组,从第一个元素到最后一个元素
Dim checkValue As String
checkValue = Trim(arr(j)) '去掉元素值两端的空格
Dim wb As Workbook
Set wb = Workbooks("xxx工作簿.xlsx") '打开名为xxx工作簿的工作簿
Dim ws As Worksheet
Set ws = wb.Worksheets("Sheet1") '打开该工作簿的名为Sheet1的工作表
Dim findRng As Range
Set findRng = ws.Range("A:A").Find(What:=checkValue, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) '在A列查找checkValue
If Not findRng Is Nothing Then '如果找到了checkValue
Dim rowNum As Long
rowNum = findRng.Row '获取该值所在的行数
Cells(i, "N").Value = rowNum '将该值所在行的值赋给M列对应行的N列
End If
Next j
Next i
End Sub
阅读全文